HOW CAN I APPLY CONDITIONAL FORMATTING BASED TO AN ENTIRE ROW?

[Guest]

I have a reasonably large spreadsheet (about 2000 rows) and i need to format
each row based on the value of a cell in a specific column.

For example, if there is a "B" in Cell I20, I need to fill the whole of Row
20 with the colour green. I then need to apply this to every row.

Similarly if there is a different letter, I need to make the row a different
colour...

All help gratefully appreciated!

J

 

[Guest]

Assuming you have three or fewer conditions, then conditional formatting can
handle this. In A20, for example, set the first CF to read 'formula is'
=$I20="B".
You can fill this across and down. Additional conditions can be applied
just like normal.

 

[Tom Ogilvy]

If you have only 3 letters to react to, you can do this with conditional
formatting.

Select your row with the activecell in column A for instance.

in conditional formatting, change Cell Value is to formula is, then enter a
formula like

So assume we have selected rows 1 to 100 and the active Cell is A1.

=A1=$I1

then click the format button and choose the pattern.

 

[Guest]

Fantastic! I knew it had to be something simple.. If I have more conditions,
do you have any ideas?

Also, I know this odd but is there a way of doing this in reverse?... i.e.
if it is green insert a "b"?

J

 

[Guest]

Some additional resources for conditional formatting:

http://mcgimpsey.com/excel/conditional6.html

http://www.xldynamic.com/source/xld.CFPlus.Download.html

 

[Tom Ogilvy]

sorry, formula should have been

=$I1="B"

 

[Tom Ogilvy]

You would need a macro to react to a color.

 

[Jim]

Assuming you have three or fewer conditions, then conditional formatting can
handle this. In A20, for example, set the first CF to read 'formula is'
=$I20="B".
You can fill this across and down. Additional conditions can be applied
just like normal.