Conditional Formatting Color Changing?
May 28, 2014
We have a spreadsheet that is sent to a manager weekly. The manager takes the sheets from 30+ individuals and copies all into a single workbook that is then distributed to a very large audience and reviewed weekly. In this workbook, I have created drop downs with conditional formatting - Low = Green, Medium = Yellow, Critical = Red. On the original workbook, this formatting works great, however, after the manager consolidates and redistributes all worksheets the Green shows Gray, Yellow is OK and Red shows Black.
View 2 Replies
ADVERTISEMENT
May 29, 2012
I have a spreadsheet that uses VB macros to calculate sums of cells based on the font color of the numbers inside. It used to be fairly easy going through each cell and "classifying" them by color, so that my macros can go ahead and sum the numbers in each respective color's cell... but now I have a huge amount of numbers and would like to automate the process somewhat. Here's an example using the A and B columns:
flight $400
hotel $150
hotel $130
meal $20
meal $15
flight $350
I tried using conditional formatting to automatically change the color of the adjacent cells based on the presence of a keyword such as "flight" or "hotel", but this change is only cosmetic, and doesn't actually change the font color (it is still the default black, hence why my color-summing macros won't work!).
I'm including a sample macro for what I use to color-sum my cells, but what I am looking to automate the color-coding process based on looking for keywords as explained above in my example. Here is one of the working color-summing macros (for red, in this case) if you'd like to use it as a reference:
Function SumRed(SelectedCells As Range)
' Adds the values of the cells where the font colour is red(3).
Dim Cell As Object
Dim x As Double
[Code] ...........
View 9 Replies
View Related
Apr 8, 2009
I want every even row that has a value in it to have a different fill color (same color for all). how to do it? I am using 2007.
View 5 Replies
View Related
Sep 29, 2009
My nominal number is 2.000 and in column A, it can not be larger or smaller than .005 which is in column C. If it is larger than 2.005 or smaller than 1.995, I would like to change the cell column B color to red. I have tried =abs(a1-b1)c1. but does not do what I want it to.
View 9 Replies
View Related
Sep 13, 2009
There is a table [A8 TO D60] and a column alongside, where the months of the year are listed. In a particular independent cell, the name of any month can be chosen and entered.
The table rows preceding the chosen month, have to be shaded. When a different month is chosen, the shading should now cover the new set of rows preceding this different month and so on.
View 8 Replies
View Related
Dec 22, 2011
My VBA code should post the formula "=IF($B6=""Store URL"",TRUE,FALSE)" into the conditional formatting formula box but everytime it pastes something different, such as "=IF($B11="Store URL",TRUE,FALSE)"
The full code is:
Sheet1.Select
Set ConditionalFormattingRange = ActiveSheet.Range("B6:F505")
ConditionalFormattingRange.FormatConditions.delete
[Code].....
View 5 Replies
View Related
Dec 18, 2013
I have a column of student grades & accumulated credits.
1.) I would like to apply a CF formula which turns the cell GREEN when a score is above 65 else RED below 65.
2.) Also I would like to turn another cell GREEN if a student has accumulated the required # of credits listed in another cell else RED.
View 4 Replies
View Related
Sep 18, 2006
I would like to change the number format of a cell in function of the value of another cell. For example, if A1=1, then I would like the cell A2 to be displayed as a percentage. If A1=2, then would like the cell A2 to be displayed as a number.
View 4 Replies
View Related
Jan 8, 2014
I have a horizontal bar chart in which some of the bars represent positive values and others represent negative values.
How to format the bars so the "positive" bars are shaded in one colour and "negative" bars in another colour? I'd prefer not to change the bar colour manually as the values change frequently.
View 2 Replies
View Related
Oct 3, 2009
If a cell cntent includes a symbol [as an arrow, for instance] beside the text,the font colour does not change as required when the condition is met.
View 2 Replies
View Related
Sep 10, 2013
I have figure A1 column which total amount accounting at cells A31. I need figure 360 as a benchmark calculation perfectly been done. In this calculation 360 well off inside cells A18 although actual number is 363. If we use Conditional Formatting when had enough total in cells the achieve 360, cells colour will be transformed to red. What formula which need I used to change no matter cells on A1 to A30 if sufficient study 360 automatically cells changing colour to red
View 5 Replies
View Related
Jun 11, 2014
Color Scale Conditional Formatting
I want to use formula to define Minimum, Midpoint and Maximum in the color scale, but am not able to achieve it.
My Criteria are as follows;
Minimum: Less than equals to 50%
Midpoint: More than 50% less than 70%
Maximum: More than 70%
I need this formula for Accuracy column only.
View 3 Replies
View Related
May 19, 2009
I am using the following formula on a conditional formatting to color cell A1 based on cell N5 date =$N5=TODAY()-2
The problem is that I need this formula to consider only weekdays, how can apply formula weekdays to my formula?
View 6 Replies
View Related
Aug 11, 2009
When using conditional formatting, I have it set to the following:
C2=60 (Reference Cell)
C3=5 (Reference Cell)
Conditional Format Settings
If C3 >= C2/12 then pattern set to Green
If C3 < C2/12 then pattern set to Red
Here is the problem - when you have a value in C3, everything is fine, it's either green or red. If C3 is blank, it defaults to green because the conditional format is true.
Is there anyway to add something in there to have no color when C3 is not populated?
View 11 Replies
View Related
Dec 11, 2013
I have a column that when I put info in it gives me a colour, now on this I have quit a few different labels, eg. AAG201, PRA001, 000010 and so on, now on my rule I have put in that if it is PRA* ,AAG* to be one colour and 0000* to be a colour. (this is working without a problem).
I have tried to get the rest of the row to be the same colour, but cannot get te rules to work, I do not know which rule to use to get this working.
View 10 Replies
View Related
Jan 19, 2012
I need to assign 3 critera for Red and similarly for Green 3 Criteria ..
For ex: Red:"3yr"≤40%
View 9 Replies
View Related
Aug 13, 2007
Is there any way to count a list that has been highlighted by conditional formatting?
View 9 Replies
View Related
Jul 6, 2009
I'm using Excel 2003.
I have four conditions that dictate font colors in column "d" of "sheet1" and am using the following
Private Sub Font_Change(ByVal Target As Range)
Set Myrange = Range("D2:D1000")
For Each Cell In Myrange
If Cell.Value = "Started" Then
Cell.Interior.ColorIndex = 3
End If
If Cell.Value = "Pending" Then
Cell.Interior.ColorIndex = 4
End If
If Cell.Value = "On-going" Then
Cell.Interior.ColorIndex = 18
End If
If Cell.Value = "Completed" Then
Cell.Interior.ColorIndex = 6
End If
Next
End Sub
1. Does this code look valid?
2. Do I paste the code in a "module" or in a worksheet object? If I add this to a module, how does the code know to reference sheet1?
3. Is there a handy reference guide that shows color codes? If so, where can I get a copy.
View 9 Replies
View Related
Jun 22, 2006
I am trying to write a VBA code to color cells.
In Column I
I need a formula to change the color of the cell based upon the following criteria
In cell I3, if J3>7 change color to teal, if H3<$A$1 color cell red, if J3<7 color cell yellow, if J3="COMPLETE" color cell grey, otherwise do nothing.
View 6 Replies
View Related
Jul 17, 2006
I found in the web-site a great VBA code that replaces the "Conditional Formatting" option in Excel. The problem is that it changes the "fill color" and I also want to change the "font color".
Private Sub Worksheet_Change(ByVal Target As Range)
Dim icolor As Integer
If Not Intersect(Target, Range("C4:C14")) Is Nothing Then
Select Case Target
Case 0
icolor = 2
Case 1
icolor = 4
Case 2
icolor = 39
Case 3
icolor = 45
Case 4
icolor = 37
Case 5
icolor = 15
Case Else
'Whatever
End Select
Target.Interior.ColorIndex = icolor
End If
View 2 Replies
View Related
Feb 14, 2007
suppose I have the numbers 1-25 in a column. I want to color the numbers 1,4,7,10,13,16,19,22,25 green, color 2,5,8,11,...25 orange and color 3,6,9,12...25 red. I can not set up the conditional formatting formulas correctly.
View 2 Replies
View Related
May 25, 2007
I'm trying to make a tracking sheet of upcoming annual inspections for different pieces of equipment. I've already got a column set up listing the inspection due date. Using three conditional formats, the individual cells change color based on the amount of time until the inspection is due (green normally, yellow at 30 days out, red past due).
What I'd like to do is have the entire row of information change color based on the color of the cell the inpection due date is listed in. For ex. J6 lists the inspection date as 24-Feb-07 and is therefore red, I'd like A6-I6 and K6-P6 to change to red as well. And when the inspection is compleate and I manually change J6 to say 25-May-07, I'd like all of row 6 to change green based on the fact that J6 is already going to.
View 5 Replies
View Related
Jun 24, 2008
Currently I have used all 3 conditional formats to colour certain cells.
1) Colour whole row 'green' when 'complete' colum ="Y"
2) Colour todays date column black in the date table to easily define todays activities.
3) By entering start and end dates that contractors are required, I colour these selected dates in blue with a letter 'x' in the calendar area.
This can easily be seen from the sample sheet above. If you can adapt this for yourselves carry on.
I am also using a macro (found in these forums) to automatically change the colour of the contractors tab to certain colours when selected from the drop down selection box (enabling more than just 3 conditional formats)
What I would like to happen though, is that when item 3 above is performed (select a start and end date) the filled blocks, colour the same as the contractor colour, instead of the blue with the 'x'.
View 5 Replies
View Related
Mar 19, 2013
How to copy and paste conditional formatting with our changing the cell values. My first row of data starts in row 4 and here is what I have in D4
If D4 is less than or equal to AA4, AW4, BS4, CO4 then it will highlight D4
Now I want to copy that format and paste it for all rows in column D. The problem I'm having though is when I paste the formula it doesn't change to match the row I'm on. For example, when I paste it in D5 this is what I get
If D5 is less than or equal to AA4, AW4, BS4, CO4 then it will highlight D5
How do I paste it so that it will update to show AA5, AW5, BS5, and CO5. I want to be able to do this for 500 rows.
View 6 Replies
View Related
Dec 18, 2013
When I hit the macro code you see below I get both columns D and E, starting from row 18, to get 'filled down' to the specified spot. Every second row has a conditional format (when a value is entered in column A) to change the row to the color grey, and every row between it has a conditional format (when any value is entered in column A as well) to have the row changed to the color red. The issue here is that the Macro code messes up the conditional formatting and uses the conditional formatting of those two cells, which are being dragged down, for those entire filled-down columns! This is what I am starting off with test1.xlsm and this is what I end up with using the macro code below (or doing it manually)
test2.xlsm
Is there a way for the Macro code to bypass this issue?
View 6 Replies
View Related
Aug 9, 2013
Summary of performance of various products against target is as follows,
Product vs Target
Color Code
Result
CH4OH
Green
1.0
[Code] ........
I need the final result automated as follows,
If 2 green of the 4 products, then final result Gree
If 2 Amber of the 4 products, then final result amber
If 2 Red of the 4 products, final result Red
Is there a way to automate this?
View 8 Replies
View Related
Feb 4, 2007
I have one cell (M6) wich will have one of the following values:
Pale Yellow
Yellow
Yellow Orange
Orange
Red Orange
Red
Red Violet
Violet
Blue Violet
Blue
This cell is filled in by a formula.
I would like this cells (M6) color, to change to the approriate color.
View 9 Replies
View Related
Apr 4, 2007
I have conditional formatting set to paint the cells a certain color if the values are >0 and a diff. color if they are <0. If i enter the value manually everything is fine but if a use a formula to do the calculation there is no formatting.
View 12 Replies
View Related
Dec 3, 2009
Is it possibe to change another cells color (Fill) based on what is placed in that cell? Example: A1 =if there is an "x" in A2 highlight A1 Green, but I have information in A1 also. Or if I had a different cell say= if there is an X in A1 Highlight A1 Green ect.. This key is to highlight a cell that I have information in based on a different cell having an X in it. If ther eis no X in the cell I have indicated, nothing needs to happen.
View 2 Replies
View Related
Mar 6, 2013
formula I colored only if three numbers in a list.
attach file
formattazione.xlsx
View 5 Replies
View Related
