Fill colour red

[Dave]

Hi all

Using Excel 2002

In SHEET1 column (AC) is text values with duplicates, In column (AA) is
numbers. What I'm trying to do is if a text value as The number 1 to the
left of it in (AA), in the next Text cell down in (AA) with the same value
make the Fill colour red. and apply this to the sheet to work when new rows
are added.

AA AC
1 JOHN ( Would be Fill colour Red because AA4 = 1
2 JANE Would Not be red
0 JANE Would Not be red
1 JOHN Would Not be red because AA5 is not a number 1
0 JOHN ( Would be Fill colour Red because AA7 = 1
5 JANE ( Would be Fill colour Red because AA8 = 1
1 JOHN Would Not be red
1 JANE Would Not be red

Thanks in advance

Dave

 

[David McRitchie]

You can use Conditional Formatting up to 3 color assigned
for interior not counting default.
http://www.mvps.org/dmcritchie/excel/condfmt.htm

Should you decide you need more than 3 conditions per cell
the next step is to use a Change Event Macro.
http://www.mvps.org/dmcritchie/excel/event.htm#case

I can't give you the formulas because I do not understand
your example, since I can't figure out what address anything
is in. It looks like you made an example and then inserted
rows.

If you want to color cells in column AC with names
based on the number 1 being to the left and down 2 cells,
you would use the formula as follows:

Select column AC as these are the cells you want to be colored
cell AC1 should be the active cell, which is important because
your formula is relative to the active cell for cells included in the selection.
Format, Conditional Formatting,
Formula 1 is: =OFFSET(AC1,2,-1) down 2 cells, left 1 cell
from the big button, choose patterns, color red

 

[David McRitchie]

Omitted the actual testing to 1, the formula should have been
=OFFSET(AC1,2,-1) = 1

The object of the formula in Conditional Formatting, Validation,
or Filtering is to return a True or False condition.