Conditional Formatting On Large Spreadsheet - 4 possible outcomes

Hello all,

I have a large spreadsheet and I want to create a rule that colors a range of cells into one of four colors, depending on the value of a cell in column "R."

Please Login or Register  to view this content.

"Red"

Please Login or Register  to view this content.

"Yellow"

Please Login or Register  to view this content.

"Green"

Please Login or Register  to view this content.

"Orange"

Applies to following range:

Please Login or Register  to view this content.

But as I mentioned, this spreadsheet is 100's of rows long so I need a quick way to apply the following rules above. Also, the cell and ranges change by a factor of 10.

So values would change like:

Please Login or Register  to view this content.

and it repeats like that for a very long time.

Same thing with the range:

Please Login or Register  to view this content.

again and again.

Is there is a quick way to do this, right now i'm manually adding each rule and changing the references and it is very time consuming.

Thanks!

This is basically how I am doing it right now. I was really hoping there was a quicker way.

Attached is what I was expecting, the name in column R could lead to four different colors. The real spreadsheet is significantly longer but I think the spreadsheet I attached gives a good idea of what I am looking for.

I had a similar issue that someone on this forum helped me with on some IFERRORS that changed by "10s" and I tried to incorporate that into conditional formatting but I couldn't get it to work.

Please Login or Register  to view this content.

Originally Posted by FDibbins

OK so your concern is how to reference just that cell that contains the name in each range?
Is the range the same size each time?

Yes & yes.

instead of R3 being fixed you cold get away with one conditional format for each person, applied to the whole sheet range

instead of =$R$3= "Strum, James"

=IF(MOD(ROW(A1),10)<>0,OFFSET( $R$3,INT(ROW(A1)/10)*10,0),"")= "Strum, James" and choose a colour

If there are more than 5 people a vba approach may be possible that chooses the colours

It assumes the ranges are the same size each time

It works, thank you so much!