Conditional formatting

[phillycarol]

I'm using conditional formatting on my cells to highlight the cell yellow if it has an "O" in it and highlight it red if it has a "C" in it.

I have rows of data that look like this
Data1 O O O O O
Data2 O O O C
Data3 O C O O O O

I want the first cell in the row to turn blue if the last cell in the row with data in it has the letter C. So in the above example, Data1 and Data3 would remain white and Data2 would turn blue.

Any tips on how to do this?
This message was edited by phillycarol on 2002-09-20 13:14

 

[plettieri]

[No message]

 

[plettieri]

Is the sample contents of the cells "000C" or is there a "0" or "c" in each cell?

I did not catch what was in each cell...

 

[phillycarol]

Sorry that wasn't clear. Each cell has either the letter "O" in it or the letter "C" in it. So if I put column headers at the top of my cells in the last example it would look like this:

A B C D E F
Data1 O O O O O
Data2 O O O C
Data3 O C O O O O
This message was edited by phillycarol on 2002-09-20 13:24

 

[phillycarol]

That didn't work - but column A contains (Data1,Data2,etc.) and the remaining columns contain one letter (O or C) each or they are blank.

 

[phillycarol]

bump - just moving this up to see if anyone can help. Thanks!

 

[PaddyD]

Hi - see the example;

Book9
	A	B	C	D	E	F
1	Data1	O	O	O	O	O
2	Data2	C	O	O	C
3	Data3	C
4	Data4	O	O	O	O
5	Data5	C	C	C	C	C
6	Data6	O	O	O
7
8
Sheet4

To recreate this:

1) select a1
2) highlight down as far as you need
3) go to format - conditional formatting...
4) select formula is
5) enter the following:
=OFFSET(A1,0,COUNTA(1:1)-1)="C"

The formula finds the value that is,eg, on row 1 a many cells away from A1 as there are data (less one cell to compensate for the entries in col a itself) & check if it = "C" .

If your data starts in row 2, use:
=OFFSET(A2,0,COUNTA(2:2)-1)="C"
etc

6) set the background pattern to blue.

note - this method will not tollerate gaps in the data in the rows, but we can deal with that if neecessary...

Post if you need more,

Paddy
This message was edited by PaddyD on 2002-09-25 19:59

 

[PaddyD]

Ooops - just re-read post & noticed that you've already said there might be blanks! Change:

=OFFSET(A1,0,COUNTA(1:1)-1)="C"

to

=OFFSET(A1,0,MATCH(REPT("Z",10),1:1,1)-1)="C"

Paddy

 

[phillycarol]

Thank you!!!!!!

I'm not sure if I understand how it works but it works. Later on today I'm going to take a look at those functions to figure them out. I love learning new Excel tricks and the boss is impressed because he likes color charts.