code

[cmccabe]

Is it possible to use VBA to check weather a specific cell (D1) within a range of cells (D1:D20) mets a certain criteria? The criteris is going to be different for each cell in the range. I dont want to use conditional formatting as I am hoping to use this code many times.

For example,

D1 = 3.0 (formula D1 >=2.0 <= 3.0, then turn cell green)
D2 = 1.8 (formula D2 <2.0 >= 1.0, then turn cell yellow)
D3 = .08 (formula <1.0, then turn cell red)

Thanks.

 

[Smitty]

The criteris is going to be different for each cell in the range.

Why?

Does that mean that the three criteria above can't be combined, because it looks like they can...

Using code for this kind of thing is relatively inefficient, especially if you're evaluating formula results, which means you'd probably want to use the Calculate event. Unfortunately, that means evaluating each cell in any given range. If you don't use event code, then you'll have a static rountine that you need to remember to run all the time.

Conditional Formatting is probably the best way to go, and it can support up to 64 conditions.

 

[cmccabe]

Can conditional formating be part of a macro? I was going to record a macro for formating the cells and would like to have the formulas check the values when the macro is run? Thanks.

 

[Smitty]

You can use Conditional Formatting in a macro, but if you insist on code, then I'd use a Select Case structure.

Here's how you can perform multiple conditional formats with a Change event:

<font face=Calibri><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br>    <SPAN style="color:#007F00">'   Multiple Conditional Format</SPAN><br>     <SPAN style="color:#007F00">'   Only look at single cell changes</SPAN><br>    <SPAN style="color:#00007F">If</SPAN> Target.Count > 1 <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>    <SPAN style="color:#007F00">'   Adjust conditions to suit</SPAN><br>        <SPAN style="color:#00007F">If</SPAN> Target > 100 <SPAN style="color:#00007F">Then</SPAN> Target.Interior.ColorIndex = 0<br>        <SPAN style="color:#00007F">If</SPAN> Target = vbNullString <SPAN style="color:#00007F">Then</SPAN> Target.Interior.ColorIndex = 0<br>        <SPAN style="color:#00007F">If</SPAN> Target > 0 And Target <= 90 <SPAN style="color:#00007F">Then</SPAN> Target.Interior.ColorIndex = 3    <SPAN style="color:#007F00">'   Red</SPAN><br>        <SPAN style="color:#00007F">If</SPAN> Target >= 91 And Target <= 95 <SPAN style="color:#00007F">Then</SPAN> Target.Interior.ColorIndex = 6   <SPAN style="color:#007F00">'   Yellow</SPAN><br>        <SPAN style="color:#00007F">If</SPAN> Target >= 96 And Target <= 99 <SPAN style="color:#00007F">Then</SPAN> Target.Interior.ColorIndex = 50  <SPAN style="color:#007F00">'   Green</SPAN><br>        <SPAN style="color:#00007F">If</SPAN> Target = 100 <SPAN style="color:#00007F">Then</SPAN> Target.Interior.ColorIndex = 5     <SPAN style="color:#007F00">'   Blue</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>