Apply Color Coding To Dates Based On Conditioning
Aug 1, 2007
Im working on a large workbook with multiple worksheets. Each of these
worksheets has a large amount of data relating to training records. In
particular, the date that an employees certification is due to expire
(eg: 01/02/2007 or 31/09/2009 etc)...(im also in Australia so my date formats may be different).
I need to color code these records with the below logic, eg:
If Cell Date is <Today() = Red Text or Cell
If Cell Date is <18months from Today()+1 = Yellow Text or Cell
If Cell Date is >18months from Today() = No change - leave white
I have basic VBA knowledge, but will be able to work my way through
some of the code that is posted.
It's also key to note that there are multiple 'Blank' and 'Text' Cells
in these worksheets. I only want to apply this code to a cell if it is
populated with a date, is this possible ?? (eg: i only want to change
the color of the dates listed, not the other cells with text and single
numbers).
I have played around with the Conditional Formatting but i will need
more than 3 conditions in the future. I've also attached a small
screenshot of the data for your convenience.
View 14 Replies
ADVERTISEMENT
Mar 29, 2012
I have a worksheet set up where one column of data (we shall call it "A") is the result of several other columns' calculations (uses a formula referencing other cells). I have an entirely separate cell that also gets its data from other cells (we shall call this "B"). What I'm trying to do is make the shading of column "A" dependent upon data from column "B". For example, if column "B's" value is >24, I want column "A" to shade in red.
View 10 Replies
View Related
Jan 28, 2013
I would like to automatically color the fill of a cell based on the cell contents.
For example, if the cell content is "1" I would like the color of the cell to be cyan and if the cell content is "2" I would like the color the cell to be red.
I've tinkered with several different formulas but I just can't get anything to work.'
View 2 Replies
View Related
Apr 14, 2008
1.- I have in any cell the name of the month ( example :A1 )
2.- In other cell I have to put this words . . " YES" , ( example B02).
3.- I need the cell "A1" fill with a color
a) if in the cell "B2" dont have any word... then FILL the cell "A1" with Yellow color
b) bu if in the cell "B2" I put the word "YES" then do nothing with the cell "A1"
View 9 Replies
View Related
Mar 16, 2012
I have a column in a spreadsheet that tells me when we ask for a check. I would like to take another column which has the date of when we receive the check to change colors based on whether it is within 30 days of the "ask for check" column and then another color if it is over 30 days. Let me try to do an example below:
ask for check Date Received
1-10-12 1-30-12 would be blue because it before 30 days
1-1-12 3-1-12 Would be red becaue its over
View 1 Replies
View Related
Aug 19, 2013
How would I make cells in column A to C be highlighted yellow, of which is 0.02 in value be higher than in compared with the adjacent cell in column D ? It is easy to use condition format function to setup the conditions in 1 row. The problem is there are thousands rows in the sheet(of which represents an individual file by each row), and need to have the conditioning format applied on each row independently. How can it be done w/o the need to have format painter applied line by line, of which is a very time consuming.
View 6 Replies
View Related
May 14, 2007
i'm trying to figure out a formula that if a cell is greater or equal to another cell - color a cell "GREEn"
otherwise...color the cell "red"
View 9 Replies
View Related
Jun 13, 2007
I am trying to figure out how to run a macro for 1 particular workbook in an excel spreadsheet. I don't want it run on any of the other workbooks in that file, just the 1. I can't use conditional formatting because I need more than 3 values (if statements).
This is what one example of data in a cell and below is the color I'd like it to turn when I run the macro:
T: 6/1/07
A: 6/8/07
Anything that has a A: (which means an actual date it happened) I'd like the cell to turn blue. If there is an RT: (which means revised target) I'd like the cell to turn red which means it missed it's target date, and has been revised. If it has a TBD I'd like the cell to turn pink (or yellow or any color really). All other cells are just white. I don't know where to find the color codes in excel as well. Other samples of what cells look like are below.
Here is another example:
T: TBD
And final example:
T: 6/4/07
RT: 6/15/07
View 9 Replies
View Related
Feb 7, 2008
I have a spreadsheet with cells containing different names. I want all of the cells containing the word Volitility to be colored one color, all of the cells with the word Momentum colored another color, and so on. I have more than three names so I cannot use conditional formatting. Attached is a workbook that has the uncolored cells in worksheet 1 and an example of what I want it to end up looking like in worksheet 2.
View 9 Replies
View Related
May 15, 2007
i'm trying to figure out a conditional formatting that if a cell is greater or equal to another cell - color a cell "GREEn"
otherwise...color the cell "red"
View 7 Replies
View Related
Oct 30, 2013
I am having an excel sheet where I enter the delivery dates. There are few freezes and restricted dates.
When I am entering the delivery date which falls under the freeze or restricted date, the colour of that cell should change.
How to achieve this either using some macro.
View 4 Replies
View Related
May 14, 2008
I have dates in Column D (mm/dd/yyyy). I'm trying to write a macro to compare the date in any cell in Column D to the current date. Then, if the date in that cell is within a month of the current date, color it red. If the date is within two months, color it orange. Etc, for up to six months. I've read up a little on dates in VBA, but I'm pretty lost.
View 4 Replies
View Related
Oct 7, 2009
The following code filters my sheet to show only values less than 0 in column T, (column 20).
View 5 Replies
View Related
Dec 4, 2012
I know how to set a conditional for a cell to change a color when i apply a specific value, but how about if i want to set E2 cell to change to green when i place a 'X' on F2?
Pretty much I want 2 columns that say Yes and the Other No. When i place a X on Yes that other cell turns green, if i place a X on No that other cell turns red.
View 5 Replies
View Related
May 22, 2014
I m unable to apply conditional formatting with numbers ( font and fill of same color).
like:-
if press 1 , cell and font should be of same color and if 2 with different color and so on
as of now either formula is applying on cell or font but not on both
View 1 Replies
View Related
Jun 19, 2014
I wish to set 2 different cases (good and normal sales) for a list of data.
During good sales
- fruits can be sold within 4 hours
- Fruits sold off during peak season (Definition of peak season: 2 calendar days before or on public holiday or 10 days before 31st May)
- < 2 fruits per type
Date of public holiday for example: 1 Jan 2014, 18 Apr 2014, 1 May 2014
For normal sales
- fruits only able to sell of the next day
- Sell of on non-peak season
- > 2 fruits per type
Sample file attached.
View 14 Replies
View Related
Feb 11, 2014
I am trying to build conditioning formats for a Financial template.. This is my formula
'=IF(c21<0.1,"RED",IF(c21<-0.01,"BLACK",IF(c21<1,"YELLOW","GREEN")))
It should display the following. I would like to use the icon sets that are color coded. I seem to have some error or overlaps.
GREEN = On track
³1.0
YELLOW = Slightly behind schedule/budget
³0.85 but <1.0
RED = Needs immediate attention
³0.65 but <0.85
BLACK = Needs to be killed or restored
<0.65
View 4 Replies
View Related
Apr 11, 2008
I have got a userform with lots of controls,
One of the action's on a large group of the controls is the same but except for one number
here is an example
If TextBox107.ForeColor = 255 Then ActiveCell. Offset(0, 53).Font.ColorIndex = 3
If TextBox108.ForeColor = 255 Then ActiveCell.Offset(0, 54).Font.ColorIndex = 3
If TextBox109.ForeColor = 255 Then ActiveCell.Offset(0, 55).Font.ColorIndex = 3
This makes a cell that correlates to the textbox red if the text in the textbox is red.
Now, I loads of these textboxes that all need to run the same code with just the Offset value one digit higher than the last and I was hoping I could create a loop to avoid a huge block of code but I can't work out how to make a constant that will +1 with each loop.
Also, can I assume that a loop will start with the control with the lowest number i.e. Textbox1 and then work its way through the rest of them in order?
View 3 Replies
View Related
Jan 15, 2009
I wrote a macro to color the cell values in the rows based on their average value. For eg if the cell value is less than 0.2 Avg, they should be red color,if value is between 0.2 and 0.5 it should be yellow. This part is working fine
Now based on the color of the rows cells , need to write a macro for the header one. Logic is Coloum header should be in red colour, if in one or more number of rows cells are red. same with yellow ones. Could you please help me out in solving this with logic.
View 8 Replies
View Related
Oct 27, 2009
I have attached the relevant spreadsheet for which I need to alter the color of the columns based on Site number ( Sheet 1). % Mortality will be represented in the Y-Axis, and the Site numbers would be on the X-Axis. All columns (% Mortality) except one will be of the same color, and the one of a different color will indicate a specific site. As an example, site 86 is colored differently. The way I require the chart to look is shown on Sheet 1.
After reading through some great posts on Ozgrid, I managed to do this using conditional formatting (Sheet 2), but that sort of falls short because I am required to add a data table to the chart, and the parameter that is indicated by the column bars happens to appear twice in the data table.
I was wondering if this can be automated maybe using VBA, but with the possibility of simply matching the color of columns with the font color of respective entry in the data series.
View 9 Replies
View Related
Apr 5, 2012
I have , for example, 3 values (1, 2 and 3) in column D which is 10,000 rows long.
Is there a formula that will colour part of each row (say C1 - F1) in a different colour each time where there is a match to value 1, 2 or 3?
View 9 Replies
View Related
Nov 1, 2009
This is probably elementary, but I'm struggling and would appreciate any help as I have very little excel VBA experience to draw from.
I have assembled code which changes the cell color based on a value change in Column A. Column A will contain many different groups of repeating values. This code works well and and I have been able to figure out how to limit the number of colors to only 2. The end result is each set of similar values in column A is visually grouped by one of two alternating colors.
The number rows in the data set is variable as the data set is extracted from SAP. The number of columns is fixed.
What I want to do now is set the cell color in columns B through F the same color that was assigned to the row in column A. So if cell A3 is set to colorindex = 6, then I want to set the range of cells B3 to E3 to the same color.
Here is the code I am using to set the color of the cells in Column A:
View 7 Replies
View Related
Apr 18, 2008
I have two columns. The first one (A) contains cells that have different Fill colors. The second column (B) contains text adjacent to the colored cells. I am trying to change the color of the text in the second column (B) to the corresponding color in the adjacent cell in the first column (A). I don't think conditional formating works well in this situation. I believe the solution would be some sort of macro.
View 3 Replies
View Related
Oct 17, 2006
I have various row cells in column (F) filled with the color Green. And corresponding text in Column G. How can I change the text of that particular row to white.
i.e.: if any cell in column F is Green, change the text color of that row in Column G to white?
View 5 Replies
View Related
Nov 18, 2007
I am looking for a very simple script that will achieve the following:
On the clicking of a button, Select and shade in a cell yellow, delete the yellow shading of the previous cell. The shading & selection should move up a column of cells, 1 at a time, in the following order:
From B10 to B9, then B9 to B8, B8 to B7 etc until the selection and shading is at B2. Once it is at B2 subsequent clicks will simply keep it at B2 (the top). Thus after 8 clicks the shading & selection should travel from B10 to B2, with only 1 cell being shaded yellow and selected at any one time.
View 5 Replies
View Related
Jun 18, 2008
I have a spreadsheet that i download from the net daily, which is seperated into columns of information.
I want to be able to look down a column and mark a cell in a seperate column if the cell font text is red.
For example looking down column A ... if the font text of a1 is red then mark the cell background colour of T1 red - if a2 text colour is red then mark the cell T2 red .... etc etc.
If the font colour in a1 or a2 ... etc etc is any other colour then do nothing.
I have 5 columns I wish to look down and mark in 5 seperate columns - I have tried to do this by conditional formating but don't know the fomula for checking font colour.
View 9 Replies
View Related
Dec 17, 2013
Is there any way I can use conditioning formatting on datapoints in a graph, so that if certain points meet the criteria they will appear with another symbol?
Suppose I would like to change symbol on the datapoints exceeding the value 200 in this graph: Graph.jpg
Can I do that?
View 3 Replies
View Related
Jun 16, 2006
I wish to perform different actions depending on whether certain errors are present in a cell and I can't figure out how to do it.
Attempt (within some For loop):
othCell = ActiveCell.Offset(-95 - counter, 0)
If othCell = CVErr(xlErrNum) Then conditioncount = 0
This generates a "type mismatch" error. I also tried othCell.value in place of othCell on the second line; I don't really follow the distinction in usage for "value". I've also attempted this with Error 2036 in place of CVErr(xlErrNum). I don't know much at all about VBA!
View 2 Replies
View Related
Aug 10, 2014
I am looking to get a formula based on my spreadsheet attached
I want F collumn to add 5 days onto whatever date you put in there then correspond it to the matched date period in I2-I6 then apply the pay period from H2-H6 and put it in the G Collumn next to the date that has been input?
View 1 Replies
View Related
Jan 27, 2012
apply conditional formats to a cell, based on the entries in another, different cell?
View 2 Replies
View Related
