Conditional Formatting question

  • Thread starter Thread starter Steve
  • Start date Start date
S

Steve

I have two columns of data.

I want to programmatically create conditional formatting on the second
column where the color of the cell is based on the value of the cell to its
left. This would apply to the whole range of the second column.

I can see how to do this if I was basing the formatting on the same cell as
the formatting is to be applied to.

What is the syntax in the macro to do the formatting based on the value of
the cells in the first column?

Thanks in advance for any help you can offer!

Steve
 
Thanks for your reply.

But doesn't this compare the cell in the right column against the cell in
the left column?

What if I wanted the formatting of the right-column cell to be dependent on
comparing the cell in the left-column cell to some value?

(sorry if I'm misunderstanding your code).

Steve
 
Steve,

The easiest way is to Record a macro while you do the formatting.
For me to be more specific I would need to know the relationship you are
looking for: = to, > , < , or what?

steve
 
Steve,

Basically, I have 2 cells hidden that contains a lower limit and higher
limit, say 2.3 and 3.6.

Then, in one column C I have values, say 1, 3.2, 4, 0
In Column D I have cells where I want the color of the cell to be dependent
on a comparison.

If the respective cell to the left of the one in D (i.e., in column C) is
less than the upper limit and greater than the lower limit, the cell in D is
yellow. If it's lower than the lower limit, it's red. If it's higher than
the upper limit, than it's green.

Does that help?

Thanks!
Steve
 
Actually, how would I do the following (should be simpler).

I've never used a formula in conditional formatting, so that's why I'm a bit
dim on this one.

I want to conditionally format a whole range (a column).

If the cell has a "1" in it, I want the cell to be red.
If the cell has a "2" in it, I want the cell to be black.

Thanks again,
Steve
 
It can be done, but comparing is very vague. Why not spell out what the
conditions are.

In general, select B1:B whatever with B1 as the Active Cell

Then do format=>Conditional Formatting, change "Cell is" to "formula is" by
selecting it in the dropdown.

in the textbox to the left put in your formula - something like

=B1>A1
or
=And(B1<>"",A1<>"",B1>A1)

then select your formatting.
 
so you don't want to be dependent on the column to the left - you now only
have one column.

Select the whole column (let us say B) with the first cell as the active
cell (B1).

Format=>conditional formatting
leave "Cell Value is"
in the next block, from the dropdown select "equal to"
in the next box put in 1

click the format button, choose pattern and select red

now click OK and then Add

leave Cell Value is
in the next block, from the dropdown select "equal to"
in the next box put in 2

click the format button, choose pattern and black
then font and change the color to white (if you want to see the number)

OK your way out.

--
Regards,
Tom Ogilvy
 
Back
Top