Conditional Formatting

[Celarent]

Is there a way to select the whole range of rows and columns, and do three conditional formatting (color coded) based on one column only? I also would like to have the whole range that i selected to follow the color coding instead of just one column. Thanks.

 

[tygrrboi]

Can you give us specific information about where the columns are, what is the range address, and what are the conditions for your conditional formatting? You do not have to write it out in Excel syntax, but rather just try to explain it in words.

 

[Celarent]

I have 8 columns. I would like to create a conditional formatting that shows report that are 30 days overdue and color it green. Then another rule that will show 60 days overdue and color it yellow. Then 90 days overdue and color it red. First to 3rd column are all texts, and the 4th column is where i have the numbers of days which where i would like to base my conditional formatting. So 4th column will show numbers like 70, 8, 32 etc.

What i did is select the whole 8 column and the corresponding rows where there is info. Then i click on conditional formatting and new rule. I picked used formula to determine which cells to format, so i select that 4th column then put >30, for the green color; same column then >60 for the yellow; and same column >90 for the red column. It doesn't work. I would like the whole rows to follow with the color coding too, not just the 4th row. Thanks.

 

[tygrrboi]

Conditional formatting formula rules require an active cell. Even if you select a large range like you have in the example above you will still have an active cell. More often than not it is the top left cell of your selection (you can tell because it is selected in a slightly different color than the rest of the selection...)

To translate your formulas exactly. (and assuming it starts in A1 because you haven't given me any cell references to be more specific...)

=A1>30
=A1>60
=A1>90


Even though it just says A1, it will apply itself to all the cells in your selection similar to how dragging a formula down applies to the new cells its in.

Also, similar to dragging a formula, you can lock rows, columns, and both of references you use in conditional formatting... (with a $)

Let's say you are applying the whole range to the values in column A... even if its B, C, D, E, etc... you want to look for a 30 in column A...
You can say =$A1>30

Or to look at the first row ONLY out of a number of columns, you can do =A$1 > 30

To show an example of how you can lock both the row and column... Let's say the 30, 60, and 90 rule are fine for now but you might want to change the values in the future... say have yellow be anything greater than 55 instead of 60... If that's the case you can have some cells on your sheet that have the conditional numbers and reference those cells in the formula.

so on your sheet...
X1 = 30, X2 = 60, X3 = 90 (they can be where ever.. not just X)

Your conditional formulas will then be something like

=A1>$X$1
this means that no matter what cell in the range you're looking at... it will always compare it to exactly X1... X1 will not move with the rest of the range.
=A1>$X$2
=A3>$Z$3


Does that clear things up?