Conditionally formatting a row with relative references doesn't work!?

[jdmc45]

Hi,

I am trying to conditionally format an entire row (and all subsequent rows) based on a criterion for the first cell to be one of either of three text values, say "a", "b" or "c". When the formatting is done however with relative references only the first cell (which the criterion is based on) is highlighted. How do I get the whole row to be conditionally formatting, with relative references, as I want to use the format painter to copy down the formats?

If someone tells me how to add the spreadsheet to a post I will include the spreadsheet which I'm sure will take one of you a matter of seconds to figure out.

Thanks!

 

[T. Valko]

Hi,

I am trying to conditionally format an entire row (and all subsequent rows) based on a criterion for the first cell to be one of either of three text values, say "a", "b" or "c". When the formatting is done however with relative references only the first cell (which the criterion is based on) is highlighted. How do I get the whole row to be conditionally formatting, with relative references, as I want to use the format painter to copy down the formats?

If someone tells me how to add the spreadsheet to a post I will include the spreadsheet which I'm sure will take one of you a matter of seconds to figure out.

Thanks!

Just apply the formatting all at once.

What version of Excel are you using?

 

[MrBill11]

=and(a1="a") in the conditional format formula might work. I had to do the same for "b", & "c" too. When copied down it worked for me.

 

[jdmc45]

That didn't work. I'm using 2007. How do I add the spreadsheet to a post?

 

[T. Valko]

That didn't work. I'm using 2007. How do I add the spreadsheet to a post?

You can't attach a file to a post.

Try this...

Let's assume you want to format the rows in the range A1:D5 if the cell in column A = A or B or C.

Select the *entire* range A1:D5 starting from cell A1. Cell A1 will be the active cell. The active cell is the one cell in the selected range that is not shaded. The formula will be relative to the active cell.

• Goto the Home tab>Styles>Conditional Formatting>Manage rules>New rule>Use a formula to determine which cells to format
• Enter this formula in the box below:
• =OR($A1="A",$A1="B",$A1="C")
• Click the Format button
• Select the desired style(s)
• OK out

 

[mikerickson]

I think you might want a mixed referencing, like

=OR($A1="a",$A1="b",$A1="c")

 

[jdmc45]

Sorry I think my explanation was poor. I would like the rows to be coloured different colours according to whether the leftmost cell of the row is a or b or c. So for example, blue for a, green for b and yellow for c. Then this format can be applied to the whole data set. My apologies for that.
Any help is appreciated. J

 

[MrBill11]

based on your last post. If you select A1 to D5, and create the following 3 different conditional formatting formulas in A1, I think is what you are looking for.
=AND($a1="a") format blue
=AND($A1="b") format green
=AND($A1="c") format yellow
Then if you highlight D1:D5 and copy it down all of the formats will follow.

 

[T. Valko]

Sorry I think my explanation was poor. I would like the rows to be coloured different colours according to whether the leftmost cell of the row is a or b or c. So for example, blue for a, green for b and yellow for c. Then this format can be applied to the whole data set. My apologies for that.
Any help is appreciated. J

OK, not a problem.

Select the *entire* range A1:D5 starting from cell A1. Cell A1 will be the active cell. The active cell is the one cell in the selected range that is not shaded. The formula will be relative to the active cell.

• Goto the Home tab>Styles>Conditional Formatting>Manage rules>New rule>Use a formula to determine which cells to format
• Enter this formula in the box below:
• =$A1="A"
• Click the Format button
• Select the Fill tab
• Select a shade of BLUE
• OK>OK
• New Rule
• Use a formula to determine which cells to format
• Enter this formula in the box below:
• =$A1="B"
• Click the Format button
• Select the Fill tab
• Select a shade of GREEN
• OK>OK
• New Rule
• Use a formula to determine which cells to format
• Enter this formula in the box below:
• =$A1="C"
• Click the Format button
• Select the Fill tab
• Select a shade of YELLOW
• OK out

 

[jdmc45]

based on your last post. If you select A1 to D5, and create the following 3 different conditional formatting formulas in A1, I think is what you are looking for.
=AND($a1="a") format blue
=AND($A1="b") format green
=AND($A1="c") format yellow
Then if you highlight D1:D5 and copy it down all of the formats will follow.

Mr. Bill Thanks for that, it was the AND function that was needed for it to apply to the entire row, cheers!