Color Scale Conditional Formatting With Relative References
Oct 7, 2013
I'm having trouble with Color Scales within Conditional Formatting. I have a data set of commodity prices. In column A I have the name of the commodity, in column B I have the standard deviation of the price change of the commodity, and in Columns C-N I have the monthly % change in the commodity price. I want to conditionally format with Color Scales each row of price changes within Columns C-N based on each commodity's standard deviation (column B). If the price change is a one standard deviation or more decrease, I want the cell to be dark red; if the price change is less than a one standard deviation decrease, I want the cell to be a gradient of light red; if the price change is a one standard deviation or more increase, I want the cell to be dark blue; if the standard deviation is less than a one standard deviation decrease, I want the cell to be a gradient of light blue; and if the price change is 0, then I want the cell to be white.
I can achieve this perfectly by manually doing 3-Color Scale Conditional Formatting for each row, but it's very time-consuming. And Excel doesn't allow me to enter relative cell references when I'm doing the Color Scale Formatting. Is there a quick way that I can do this so that each row is color formatted differently?
I've attached an example file (there are many more rows within the original file) and formatted the first several rows manually as I want the final product to look.
example.xlsx
View 3 Replies
ADVERTISEMENT
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
Jan 14, 2014
I need to color format several matrices of about 1000 rows each in order to find a pattern. Each row needs to be formatted on a color-scale so that the maximum value in each row has the darkest color and the minimum value has the lightest, while others are lighter according to their weight. It can be done on a single row, but it does not work for multiple rows at once. I have tried format copying and range options but it takes the maximum and minimum for the whole range rather than individual rows.
I'm wondering if it is possible to do it for multiple rows while the criteria of maximum and minimum applies to individual rows.
View 2 Replies
View Related
Dec 18, 2007
Is it possible to have the conditional format auto adjust the cell value range (similar to how the drop and drag with the autocomplete cell values turned on works)?
For example:
If I conditional format: A1 - does not equal A2 then change colour to blue. Then use the Format Paint to copy the A1 conditional format to B1. Is there any way that it can auto adjust the cell reference to change to - does not equal B2 (the next cell down and so on) rather than having to manually go to each cell and adjust the cell reference?
Ultimately I want the following to happen:
A1 - does not equal A2 (colour blue)
B1 - does not equal B2 (colour blue)
C1 - does not equal C2 (colour blue) etc
View 6 Replies
View Related
Feb 10, 2014
I set up formulas to count text characters in a range of cells. I'm tracking attendance and payments for a small yoga studio.
All I need to do is count "Y"s for prepaid attendance and "DI"s for drop-ins. I have the formulas working but they are absolute so inserting a row will break my sheet.
=COUNTIF(E14:Z14,"*Y*")
=COUNTIF(E11:Z11,"*DI*")
View 1 Replies
View Related
Apr 30, 2008
I am using Excel 2007.
I am using the coditional formating to highlight whether a date contained in particular cell is within 12 months of todays date or over 12 months in the past.
This is done by placing the =TODAY() in a cell at the top of my sheet and then in another cell, doing a DATEDIF calculation between the two and this works fine. I can then specify in my conditional formating to turn the cell containing the date a funny colour if the cell doing the DATEDIF calc is > or < 12. Simple!
What I can't do is copy this formatting for the other 30 rows of dates I have. When I try and copy them, it references the original cell, not the one for that row. Example: If my date I want to test is in B2 and my DATEDIF clac is in U2, when I copy the coditional format to the new row, say B3, the new rule still references U2, not U3. I understand about dymamic and static references and I have removed the '$' signs from the formula.
View 8 Replies
View Related
Feb 8, 2014
I have 2 sheets. Sheet1 looks like this:
B
D
A
[Code]....
Note: The "X"s are actually not in the cells, they are for illustration purposes only. Instead of the "X"s, I want to fill the cells marked with X with the color red. The cells in sheet2 have no value except row 1 and column 1.
I am looking for the formula to fill into the conditonal formatting field for sheet2.
The formula should detect how many letters there are in row 1 of sheet2 (for example here we have 4, namely A, B, C and D). If I add a new column E later, I just want to re-apply the formula to those new cells, not edit the formula itself. If there is a new column E, it will also be added on sheet1, but not necessaryly to the right side of the column with the header "A", maybe it will be added between "B" and "D".
The number of rows will not change, they stay static at 1-5
Here is the underlying task.
Sheet2: A column that has all days of the year, from Jan 01 to Dec 31, from top to bottom, starting in A2 going down to a366. In column B, C, D and so on, in row 1, I write the names of different countries. Sheet1: In row 1 I write the same country names as in sheet2, but not in the same order. Below the country names I list the official pulic holidays of the whole year.
I need the formula for conditional formatting that will color the intersecting cells of Country names and days of the year. And I need the formuly to be dynamic so that I can add more countries later without have to adapt the formula for new columns manually.
So far I have the formula to count the filled cells in row 1 on sheet1. I also have the formula to find the country name in sheet2 and return the column number of the same country in sheet1. Last but not least, I can make this work with a static formula, but replacing the static pieces with the dynamic pieces mentioned above just won't format the cells as desired.
View 1 Replies
View Related
Jul 18, 2014
I am working in a receiving room at a condo complex and am trying to facilitate how we handle and distribute the packages.
What I have in the first sheet of my would-be file is the unit numbers in a 28X12 array (12 units on 28 floors). I would like to make the array conditionally formatted to be highlighted in red to show that there is a package in for the unit in question. On the next sheet, I am putting information such as carrier, unit number and tracking number for each of the packages we receive. I want the unit number on the first sheet to immediately be red when the unit number is typed in the second sheet. When doing conditional formatting, it is easy to do this, by making the condition that the unit in the array will be highlighted when the unit is seen in the unit column in the second sheet.
My question is this: is it possible to somehow duplicate the references for the entire column that contains the unit numbers. I want a whole column to be the reference for conditional formatting of an array, so that I would be easily able to see who has a package in the receiving room, and who does not.
View 1 Replies
View Related
Aug 19, 2007
Does anyone know if it's possible to use R1C1 style references in conditional formatting formulas?
Eg., =R[-1]C > 1
I tried the above and I keep getting an error.
View 9 Replies
View Related
Aug 15, 2008
I am trying to use conditional formatting in Excel 2003 to indicate when certain tasks are overdue (by highlighting the cell in red). My problem is that I have multiple criteria. If my line item is a "priority" item, it is considered aged if not resolved after 2 days; if the line item is "routine", the item is not considered aged until after two weeks (14 days). My goal is:
1) I would like the "status" cell in column D (which says "open" or "closed") to turn red if the item is aged.
2) Aging depends on the "priority" status in column A .......
View 9 Replies
View Related
Dec 3, 2012
I have this fairly simple formula which decides whether to shade a cell or not
=AND($X$1<>"TBD",R3<>"None",AC3="Y")
This is set in cell R3 and I want to copy it all the way down the cells in the R column. However, when I copy & paste (and copy and paste using paste special, formatting) the R3 and AC3 cell references do not update to match their relevant rows. eg If I highlight cell R26 the conditonal formatting formula still refers to cell R3 and AC3, not R26 & AC26. I'm using Excel 2010 but I don't recall this happening in 2003.
View 12 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
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
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
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
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
Jan 24, 2010
I suspect there's a simple answer to this but I've looked and can't find it: In Excel 2003, when I add a hyperlink to another file and close/reopen my workbook, the link is saved as relative to the current workbook. So if the main workbook is moved or, in my case, when I use VBA to copy and email the sheet with the hyperlinks, the links are broken.
View 2 Replies
View Related
Jun 20, 2006
I'm having some trouble coding a macro that copies data from one sheet in a workbook to the next sheet in the workbook, and the problem seems to stem from my lack of knowledge about how VBA refers to sheets. What I'd like to do is copy data from a range of cells in the next-to-the-last sheet in a workbook to the same range in the last sheet in the workbook.
Each day, I add a blank sheet to the end of all the sheets in my workbook. I insert some data into it, then go to the previous sheet, copy some data, return to today's sheet (the one I just added) and paste the data. I can record a macro that will do this perfectly, but it only works for the two sheets in which I record the macro. I need a way to change the sheet references so it always copies from the next-to-last sheet to the last sheet.
View 3 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
