In a spreadsheet macro I'm building, I need to test the contents of one cell
for a value and if the value is true, set a background color for the active
cell based on whether its value is equal to or greater than a calculated
value derived from a value in another cell. I can't get this done, so far,
with using Conditional Formatting. Here is a visual of what I'm trying to
accomplish.

A B C D
1Wks in Mo. 4.2 4.2
2Name Type June July
3Fred INT 124 133
4Kay EXT 155 170

In this example, if Fred has a type of "INT" in B3 then in cell D3 I want to
first take the value of D1 x 30, which would equal 126. If Fred has a value
in D3 of greater than or equal to 126, then I want to change the background
color to Red.

I have 13 cells on each row that I need to do this type of calculation for.
I there is a per cell formula that I could use with Conditional Formatting,
that would be great. If not, is there a way to do this with VBA? (I'm sure
there is, but I don't have the bean for figuring it out!) I would really
appreciate some help with this one.

=AND($B3="INT",$D3>=$D$1*30)

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Big Red" <(E-Mail Removed)> wrote in message
news:BB6E1652-B9A5-4743-B7B7-(E-Mail Removed)...
> In a spreadsheet macro I'm building, I need to test the contents of one
> cell
> for a value and if the value is true, set a background color for the
> active
> cell based on whether its value is equal to or greater than a calculated
> value derived from a value in another cell. I can't get this done, so far,
> with using Conditional Formatting. Here is a visual of what I'm trying to
> accomplish.
>
> A B C D
> 1Wks in Mo. 4.2 4.2
> 2Name Type June July
> 3Fred INT 124 133
> 4Kay EXT 155 170
>
> In this example, if Fred has a type of "INT" in B3 then in cell D3 I want
> to
> first take the value of D1 x 30, which would equal 126. If Fred has a
> value
> in D3 of greater than or equal to 126, then I want to change the
> background
> color to Red.
>
> I have 13 cells on each row that I need to do this type of calculation
> for.
> I there is a per cell formula that I could use with Conditional
> Formatting,
> that would be great. If not, is there a way to do this with VBA? (I'm sure
> there is, but I don't have the bean for figuring it out!) I would really
> appreciate some help with this one.

formula is
=$d$3>126
format as desired

--
Don Guillett
SalesAid Software
(E-Mail Removed)
"Big Red" <(E-Mail Removed)> wrote in message
news:BB6E1652-B9A5-4743-B7B7-(E-Mail Removed)...
> In a spreadsheet macro I'm building, I need to test the contents of one
> cell
> for a value and if the value is true, set a background color for the
> active
> cell based on whether its value is equal to or greater than a calculated
> value derived from a value in another cell. I can't get this done, so far,
> with using Conditional Formatting. Here is a visual of what I'm trying to
> accomplish.
>
> A B C D
> 1Wks in Mo. 4.2 4.2
> 2Name Type June July
> 3Fred INT 124 133
> 4Kay EXT 155 170
>
> In this example, if Fred has a type of "INT" in B3 then in cell D3 I want
> to
> first take the value of D1 x 30, which would equal 126. If Fred has a
> value
> in D3 of greater than or equal to 126, then I want to change the
> background
> color to Red.
>
> I have 13 cells on each row that I need to do this type of calculation
> for.
> I there is a per cell formula that I could use with Conditional
> Formatting,
> that would be great. If not, is there a way to do this with VBA? (I'm sure
> there is, but I don't have the bean for figuring it out!) I would really
> appreciate some help with this one.

Thank you, Bob, this worked perfectly!

"Big Red" wrote:

> In a spreadsheet macro I'm building, I need to test the contents of one cell
> for a value and if the value is true, set a background color for the active
> cell based on whether its value is equal to or greater than a calculated
> value derived from a value in another cell. I can't get this done, so far,
> with using Conditional Formatting. Here is a visual of what I'm trying to
> accomplish.
>
> A B C D
> 1Wks in Mo. 4.2 4.2
> 2Name Type June July
> 3Fred INT 124 133
> 4Kay EXT 155 170
>
> In this example, if Fred has a type of "INT" in B3 then in cell D3 I want to
> first take the value of D1 x 30, which would equal 126. If Fred has a value
> in D3 of greater than or equal to 126, then I want to change the background
> color to Red.
>
> I have 13 cells on each row that I need to do this type of calculation for.
> I there is a per cell formula that I could use with Conditional Formatting,
> that would be great. If not, is there a way to do this with VBA? (I'm sure
> there is, but I don't have the bean for figuring it out!) I would really
> appreciate some help with this one.