Referencing an unspecified cell

**toffee_madman** · 10-05-2011, 11:47 AM

Hi All

I'm currently working on a plan to update a table, where there are 10 cells per row which are all stages of completion (If you have done stage 2, then 1 and 2 are green), if 5 are done, 1-5 are green). I've want to make it so that for example, if the person has completed stage 8, he puts a "c" in box 8, and all the previous boxes get filled in as well.

Now, i can do this in Conditional Formatting as below - where i say if the 10th cell on Row A has a "C" make the previous 9 green...

Conditional Formatting code is =$T$10="c". Then the box becomes green.

Now the problem we have is i need to do this to all 9 in the first row, as well as hundreds of rows, i would have thousands of formatting rules to add.

Is it possible to make a Conditional Formatting along these lines....

Human Speak: "If the cell to your right contains "c", then make formatting changes."

In a nutshell, can you make a Conditional Formatting something like =CELL+1= "c"

Many thanks in advance for any help you can provide.

**Whizbang** · 10-05-2011, 12:07 PM

Please see the attached for an example.

The conditional formatting used is:
=COUNTIF(B2:$K2,"c")>0

With B2:K2 selected and B2 the active cell.

**brokenbiscuits** · 10-05-2011, 12:14 PM

You could use something along the lines of (assuming the first row is K10:T10, and the last column is column T):

=COUNTIF(K10:$T10,"C")>0

Or I could refresh...

**toffee_madman** · 10-05-2011, 04:47 PM

Thanks guys, they work great for each individual row

is there anyway i can make it control all rows..

for example, excel will look at this row but also the 100 rows below to see which of those cells have a "c" and make them green on each specific row also?

Thanks for giving me the help so far though, definately given me a new way to look at this

**Whizbang** · 10-05-2011, 04:58 PM

This formula will work for this also. Instead of selecting just that single row, select your entire range. Just make sure your active cell is the top left cell. You could also copy the first row and paste special formats, if you don't have any row or cell specific formatting.