Conditional Format

Topic

New Reply

How can I set up to have a data range, say D5:K5, change color (backround or font) conditional on the result of an equation in cell A5 (the equation returns either a YES or a “”). D5 through K5 already contain other equations.
Thanks
Stephen

Reply | Quote

Replies

Select the range you want to format and goto go to Format, Conditional Formatting and change the dropdown from Value is to Formula is , and enter

=$A$5=”YES”,

and select the required formatting.

Andrew

Reply | Quote

Thanks, Andrew. I was able to set the conditional format for an entire row of my table but now I need to copy it down so that when a “yes” appears in any cell from like A5 to A25
the corresponding row will turn Blue. Since I have 5 or 6 tables to set this up for with each one having up to 40 rows, I need some way of doing it without having to do a row at a time. I tried blocking 20 rows in column B and then opening Conditional Format and it let me create $A$5:$A$25 by dragging down column A but when I entered =$A$5:$A$25=”YES” and set the format, nothing happened.
Any ideas.
Stephen

Reply | Quote

Stephen,

If you change it to =$A5=”YES”, then it should copy down the rows.

select the first row and enter that formula (notice the missing $ before the 5), copy it and Paste Special Formats to the other rows.

Hope that solves it for you,

Andrew

Reply | Quote

Andrew:
Thanks. I just assumed that it had to be something more complex so I never thought to check the $ signs.

Reply | Quote

That’s interesting about the one $ sign. Are there specific rules around using only one $ sign? eg. before or after the letter

I frequently do a lot of copying of conditional formatting. Usually I have to add or remove the $ signs depending on the effect I want to achieve and the relation to the cell I am referencing.

Reply | Quote

There are no specific rules other than those that apply to normal formulas and how they are referenced. The default is both $ signs, as it is assumed that the reference is to an absolute cell. If you want the column to be unchanged for cells in the range you put the $ before the A. If you want the row unchanged you put the $ before the number. It is not a question of before or after, the $ sign goes before the element it applies to. The sign might appear as though it is after the letter, but that is only because it is before the number.
The $ means that the element it applies to will not change if the format is copied., i.e. if the column is A, and is preceded by a $, then all cells refer to column A. Similarly with rows.

One use for relative (no $ at all) conditional formatting is to use it to highlight duplicate rows in a sorted list. If all cells in the list are highlighted you know you have an exact match.

Hope that clarifies rather than confuses,

Andrew C

Reply | Quote

Hi Folks,

I almost never use conditional formatting except in the following manner:

1) Select one cell in the range-to-be-formatted
2) Set the conditions using the conditional format dialog
3) Selectively getting rid of “absolutes” ($)
4) Copy the formatting to the other cells in the range

This simple technique takes much better advantage of the power of conditional formatting, and it took me a while to figure it out.

JIM

Reply | Quote

Hey Jimbo or is it JimBay?
If you remove the first $ sign when you are using the formula version, then the can’t copy the fomatting across the rows so that the formats are conditional on what appears in the first column. For example, I want the whole row to light up to alert the user to look at what is going on in the first column so they can take the appropriate action. What I don’t get is the following. I have “Yes” appearing, say, in A3, A5, and A9. If I block out A2:A20 (my data range), set conditional formula = $A2=”Yes” and set my Formats, it works great. But if I change it and set
conditional formula = $A3=”Yes”, the formatting totally changes (Black background and Yellow text for the “Yes” cells now becomes Black for cells with no text and nothing for the “Yes” cells. Is the convention that you have to use the top cell in a blocked out range to set the formating conditions for the entire range. Also, I tried dragging over the range to get like =$A2:$A12=”yes” and had no luck at all.

Reply | Quote

Oops. I guess there is a better way to describe what I was trying to describe. Here is “try #2”.

It is easier to think in terms of one cell when it comes to conditional formatting. Therefore, set the conditions for one cell, then Copy, and Paste Special the formats. Then, inspect the conditional format of one of the other cells you copied to, and decide if it is adequate. If not, figure out why not, and why the Copy and Paste Special didn;t work. Usually, it has to do with the absolute notations in teh original conditions. Then, go back to the first one, selectively remove the $, Copy and Paste Special again.

I have difficulty following verbal descriptions of such things, but I could probably help of you could work up a sample and post it here, with a description of what you want to happen, and what is happening isntead that you don’t want.

Jimbo is fine!

Reply | Quote

Thanks, Jim:
I got it working the way I want now. I just have to remember to go back to this file to see how I did it. I have not yet been able to get it to conditionally format an area in one sheet depending on a value in another sheet but there are many ways around this so I don’t think I will pursue it further.

Reply | Quote

Hi,
This isn’t always very practical but you can use conditional formatting based on the value in a cell in another sheet if you name that cell first. For example, if you define a name ‘test’ that refers to cell A1 on sheet1, then on sheet2 you can add a conditional format =test=4 to a cell and that will work, even though =’sheet1′!A1=4 won’t work.
Hope that helps.

Reply | Quote

True indeed. Because of my typical application of conditional formatting (changing the display of individual cells in a range based on their corresponding cells in another range), using named ranges doesn’t work. I resort to setting the conditions for one cell, with a relative reference, then copying the format to the other cells. It’s an important but subtle distinction, and it goes against my usual strategy of using named ranges whenever possible.

Reply | Quote

Jim,
Now that I think more about it, if you define the name as a relative reference (eg ‘test’ =’Sheet1′!A1), then you can use this on a different worksheet and it will refer to the corresponding cell on Sheet1 – i.e. if you use it in cell A2 on sheet2, it refers to cell A2 on sheet1. This does mean that you can use it to conditionally format a range on one sheet based on the values of the same range on another sheet (if you see what I mean)
FWIW.

Reply | Quote

How about if I want to, say, format the cell to blink in a certain color, say red?

Reply | Quote