I'm trying to make a little dynamic map of the United States where each of the states change color based on a particular cell value that corresponds to that state. I've got the map of the United States in Excel and ungrouped so I have a collection of seperate shapes - one for each of the lower 48 States. I've named each shape (state) by it's postal abbreviation: TX, CA, NY, etc.
Now I'm trying to write a macro that will COLOR the state according to a numeric value in the cell next to the cell containing the State abbreviation. These are transit days to this state that I will calculate in another location. Like this:
TX = 3 days
CA = 1 days
OR = 1 days
NY = 7 days
KS = 3 days
FL = 7 days
I currently have an issue with some of my user defined functions. These functions work perfect for everyone here at my company in the USA / UK. The problem happens when we send it to our client who is overseas.
The client opens up the file and all they see are #VALUE! errors where our UDF's are being used. But when they send it back to us the file works properly.
I have currently added VBA code so that when the workbook and the user enables macro's the file will do an application.calculatefull. This still does not seem to work for them.
The excel version that I am running currently on my PC is MS Office 2003 Pro (11.8146.8132) SP2. The version that the client is running on their PC is MS Office 2003 Pro (11.8105.8107) SP2. Could this problem occur because of updates that they have not yet installed? I have been working on this problem for about a week now and have not come up with a solution.
I am trying to insert custom formatting into a cell so when I enter a set of 9 numbers for phone numbers in the U.S. and 14 numbers for international phone numbers, it will automatically display it in the correct format. Reason for the 0's on the international is because country codes often start with a 0, so I need to ensure it isn't removed from the number.
I'm working with a pie chart that has a dynamic range as its data source and a dynamic range as its legend. The chart shows ratios of bought items depending on customer type. So, when you switch from a "Hair" customer to a "Bath & Body" customer, the ratios shift and the legend changes as well (i.e. "Hair - 25%" to "Hair - 10%").
Is there a way to fix the colors permanently so that as the ranges change, the colors remains the same for each category?
I've seen some VBA for it, but it was for data in long columns, whereas my data is in one range in the middle of the worksheet.
My pallet lost color-coding - if I hover over each little scare it displays the names for the colors and if I click on them they color the cells with the right colors, but the palette itself lost the visual display of colors except for 8 colors: black, blue, red, magenta, yellow, cyan, and white.
I use color-coding of cells a lot and I find it difficult to work without visual clues. At least the hover-support allows me to get the work done, but with difficulty.
I had a worksheet with Employee name,Place,State & Amount Columns. I want the data in the MasterSheet to be copied into Individual Sheets based on Place & State. All The rows should be copied into one sheet based on the State of the respective Place one after the other. For Excel to Know which Place belongs to which State I had created the sheet match List.
The Data is dynamic.Each Day the data may grow or shrink depending upon the transactions at that place.I had shown how the data should be copied into individual sheets.
Below are states which are located in column "I". I need a VB code that will cut any entire row that has any of the following states below into a new collective workbook - if any of the states don't exsist then to just ignore and move on. They all have to be in the same workbook in the end.
If NONE of these states exsist, please just insert a msgbox ("No states to move")
(I basically need to divide West coast and East coast by removing all WC states and put them in their own workbook)
I am trying to figure out the excel formula I need for a project. Basically i am try to do a lookup for Country/State. What I have is a column called Country and once called State on the first worksheet on the second I need to do the calculations that will allow user to select Country and the when they click on the State it shows only the related States based on the Country which are loaded on the second worksheet.
(EG.
COUNTRY = United States - will return/diplay in dropdown
I have been working on creating a dependent drop down list and have been able to create lists (Insert > Name > Define) and it has worked for all the lists except 2. It will not allow me to create a list titled C or R. I get a pop up stating "that is not a valid name". Why is that?
I have a list for each letter of the alphabet except the letters C & R. No matter what I seem to do I just keep getting the pop up window stating that it is not a valid name. I also tried closing the workbook and restarting the pc and then trying but the same thing keeps happening.Thanks!
I want to sort all of the "column2" by alphabetical order. The order will be first by state and then by city. For example the first box the column2 order should be (Fountain Hills, AZ) properties first followed by (Mesa, AZ) properties second followed by (Peoria, AZ) etc.... ending with (Flower Mound, TX) last.
The issue that I'm having is that when I create a custom sort the comma after the city indicates a new value being AZ. For example my custom sorting looks like this: Fountain Hills, AZ, Mesa, AZ, Peoria, AZ. This is limiting my ability to sort the files as is with city, state. I solved this issue by removing the commas after all the cities. This worked but was time consuming. Is there a faster way? The workbook is below.
I'm trying to get a VBA macro put together that would delete all the rows, throughout every sheet, and shift up wherever the cell in column A = "Delete Row" in a workbook I've made. In the below copy of the workbook you can see that a lot of the information from all the tables is linked to the topmost table on the 'Mix Detail Sheet'.
If the the Cell in column A is blank on that first table, its corresponding cells in tables throughout the workbook displays the words "Delete Row". I need the macro to delete the rows and shift everything up where this occurs.
Ideally I'd also like the macro to then delete the rows in that first table where the column A cells are blank.
column I row 11 has a functional argument that simply states to display the output as .843 I need to edit it to .844 and I cannot seem to find out where or howto edit it.
There are others like this that I need to do too so I need to learn how to do it. Not just have someone do it for me.
ALSO as you can see this sheet displays #N/A all over the filled in cells... I would like them to be blank until I enter some pertinent info... I tried this is cell m7 BUT as you can see in m12 it goes back to the N/A
I have a range which will change in size & in content, & I want this to be a Named Range at whatever size it is.
Reason I want to is because I want to make a Validation List with this dynamic range. I also want a Validation list which lists the content of 2 or more dynamic ranges which may or may not be on the same worksheet - is this possible?
i.e. First dynamic range: called "Milestones" at A11 Second dynamic range: called "Activities" at A25 & make a Validation list that will list content of both
On sheet 1 I have a list of 1000 firstnames On sheet 2 I have a list of 1000 emails,
I need a function that states If a cell in the email column contains a string or value from the names column, it will result in a true statement so that I can separate out the emails that have these peoples first names.
I would like to replace the blue bit of this Dynamic Named Range (DNR) with an INDIRECT formula in order to modify the starting point for the DNR: =OFFSET(DATA!$C$60,0,0,20,1). Unfortunately, I can’t seem to get my INDIRECT formula to work in order to use it to replace the blue bit above: INDIRECT("'"DATA"'!&ADDRESS(MATCH('SHEET1'!AC8,DATA_Date,0)+22,3)"). The orange bit of the formula above returns a value of 60, therefore the ADDRESS formula should return $C$60, that can then feed into the INDIRECT and act as the equivalent of DATA!$C$60. But it doesn’t.
How to change the color of a single tabe. What I'm trying to do now is reset the tab color for all tabs.
Here is my code:
'REMOVE TAB COLOR Dim ws As Worksheet For Each ws In ActiveWorkbook.Worksheets With ActiveWorkbook.ActiveSheet.Tab .ColorIndex = xlNone .TintAndShade = 0 End With On Error Resume Next Next ws
I'm new to using loops so I'm not exactly sure what the above code is doing. When I debug it goes through the steps without any errors but it doesn't actually change the tab colors.
The following code is the code "behind" the run button on my userform. The last section reads in a column ( of numbers and text) into a listbox based on the users choice. Is there any way I can have only certain rows in a different color - based on either if the value in that row is greater than a certain number of if the text in that row is a particular sentence?
Private Sub CommandButton1_Click() 'Dimension all variables Dim fqc As Long Dim eptg As Long Dim newlabel As String Dim BorC As String Dim now As Long Dim secondorthird As String
'Collect User Choices fqc = TextBox3.Value ' Final Quiz Choice eptg = TextBox5.Value ' Expected Post Test Grade BorC = TextBox4.Value ' Is desired grade a B or C now = TextBox6.Value ' Number Of Weeks after term has started secondorthird = "second" If (now = 3) Then secondorthird = "third" .....
For some reason colors will not show on any EXcel doc. When I type colored text in a cell, it shows the color until I press enter, then it goes black. Cell fill colors do not show at all.
However when I send the doc to another computer the colors wil show.
I am trying to create a Holiday/Absence Sheet that will count up hours of Holidays, absence, business Visits etc etc.
The only way I can think of doing this is by colour and filling the cells with the Number of hours holidays, Absence & Business Visits but to do this I need to create a formula that will sum by colour. I've tried a variety of things and I'm sure this can be done using VBA but it's something I have never used so I am at a loss.
So, what I need is a SUM (A1:A31 if colour= Red) and SUM (A1:A31 if colour= Blue) etc.
I hope this makes sense or one of you geniuses out there can come up with somethign smart that works better than my idea!
I have a big set of data and I want to create different graphs in different tabs with it.
So Imagine that in Tab 1 I want to do the sales forecast for the next five years for product A for all countries in Europe Tab 2 would be the same, but for product b Tab 3 the same, but for product c
So with dynamic tables in each tab I can easily do my graph
The problems is that since values vary per product country A in tab 1 is red, in tab 2 is blue and in tab 3 may not exist.
How can I set up a template so taht I do not have to color code each country in each tab? I.e. I want Spain to be blue in all my graphs in the workbook, Italy red and so on...
When a workbook is opened, (using OnOpen() ), I trying to get two header titles colors (A2, B2) to change from their usual Black to Red to Black to Red and finally back to Back.
I'm battling, especially with the time (1 second) for each change. I'm probably using the wrong approach. Can anyone suggest a sound method to achieve this?
How do I go about formatting selected cells for different colors for each different number in the selected cells? If I have numbers scattered about I want the cell to show a different color depending on the number that is inserted. EG: 115=red, 365=blue.
