Conditional formatting

phillycarol

New Member
Joined
May 7, 2002
Messages
22
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
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

plettieri

Well-known Member
Joined
Sep 4, 2002
Messages
1,556
Platform
  1. MacOS
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

New Member
Joined
May 7, 2002
Messages
22
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

New Member
Joined
May 7, 2002
Messages
22

ADVERTISEMENT

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.
 

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234

ADVERTISEMENT

Hi - see the example;
Book9
ABCDEF
1Data1OOOOO
2Data2COOC
3Data3C
4Data4OOOO
5Data5CCCCC
6Data6OOO
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

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
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

New Member
Joined
May 7, 2002
Messages
22
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. :)
 

Forum statistics

Threads
1,144,291
Messages
5,723,527
Members
422,502
Latest member
barakgahtan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top