Help!- Changing cell color

[jarahsang]

Hi excel gurus...

I am trying to figure out how to change a cell color based on th
results of color change done by condittional formating (CF).

Here's the problem...i have to columns of data column A has actua
financial numbers, and column B has forecasted numbers. the forecaste
numbers are fixed and can't be changed. the actuals are what the use
goes in and changes.

in cell A1, i have a conditional format setup. Say if value is les
than 100, cell color is highlighted green. if value is greater tha
100, cell color is yellow. Now I want cell B1, (column b is forecaste
columns, that has all forecasted numbers), to change color depending o
the color in cell A1. So if Cell A1 turns green, then Cell B1 shoul
turn green as well.

Does anyone know how to do this in VBA? I have rows of data, so woul
like to apply it through multiple cells.

Thanks for any assistance you can provide

 

[Frank Kabel]

Hi
select for example cell B1, goto conditional format and enter the
following formula:
=A1<100
- choose the format

Repeat this step to duplicate the conditions for cell B1

Another (easier option) would be:
- select your cell range. e.g. A1:B100
- goto the conditional format dialog and enter the formula
=$A1<100
- choose a format and repeat

 

[Earl Kiosterud]

Jarahsang,

To add to this, it's worth noting that the active (white) cell of your
selection is the one for which the Conditional Formatting formula must
apply. If after doing Frank's second method, you selected one of the other
cells and looked at its Conditional Formatting, you'd see that the formula
would look different -- the cell references would be applicable to that
cell, such as
=$A5<100
if you were in row 5 (either A5 or B5).

So in this case, the active cell must be in row 1 for it to work correctly
with the original formula.

The formula gets copied into the Conditional Formatting of the other cells
of the selection. The A part doesn't get changed because of the absolute
($), but the row part (1) does.