Why excel cell color did not change after formatting and VBA code?

G

Guest

I am using excel 2000 and having some problems with two particular cells
(which contained formulas) in my worksheet. I have been using both
conditional formatting and VBA to change the color of two cells according to
some conditions:

Change color in cell P18 and P21 to Green, if 0 <= P18 or P21 <= 1, else
change it to Red

I have tested both method seperately (VBA and Conditional Formatting) and
there is nothing wrong with the result except whenever the cell P18 value is
re-calculated to zero, it will ALWAYS stay as Red...

I have even try to force the formatting by giving an extra line of code to
make sure when the cell is zero, it will be red...but this is not the case.

VBA code for one cell:

Private Sub Worksheet_Calculate()

Dim ucell1 As Range
Dim ucell2 As Range
Dim lcell1 As Range
Dim lcell2 As Range
Dim tcell1 As Range
Dim tcell2 As Range

Set ucell1 = Range("R18")
Set lcell1 = Range("S18")
Set ucell2 = Range("R21")
Set lcell2 = Range("S21")
Set tcell1 = Range("P18")
Set tcell2 = Range("P21")

If (Range("P18") < ucell1 Or Range("P18") = ucell1) And (Range("P18") >
lcell1 Or Range("P18").Value = lcell1.Value) Then
Range("P18").Interior.ColorIndex = 29
ElseIf Range("P18").Value = lcell1.Value Then
Range("P18").Interior.ColorIndex = 29
ElseIf Range("P18") > ucell1 Or Range("P18") < lcell1 Then
Range("P18").Interior.ColorIndex = 3
End If

End Sub

I would be grateful if someone could provide some advices as to why the cell
stay on Red when it is zero?

Any help will be much appreciated

Best Regards
Casey Wong
 
B

Bernie Deitrick

Casey,

Conditional Formatting takes precedence over standard formatting. You need to delete the CF on the
cell to get the standard formatting to show.

HTH,
Bernie
MS Excel MVP
 
G

Guest

Hi Bernie,

Thanks for your reply. I have deleted the CF and only using VBA code still
the problem exist. It seem to be a bit strange, as the VBA code has
specified clearly which colors to use on what condition, but this seem to be
not working for the two particular cells? Am I missing something? Is this an
excel application problem or simply a setting problem?

Regards
Casey
 
B

Bernie Deitrick

Casey,

Your code only works on one cell, not two or more. Perhaps you have other code? Do you have a ogic
for your formatting?

Anyway, try this for cell P18.

HTH,
Bernie
MS Excel MVP

Private Sub Worksheet_Calculate()

Dim ucell1 As Range
Dim ucell2 As Range
Dim lcell1 As Range
Dim lcell2 As Range
Dim tcell1 As Range
Dim tcell2 As Range

Set ucell1 = Range("R18")
Set lcell1 = Range("S18")
Set ucell2 = Range("R21")
Set lcell2 = Range("S21")
Set tcell1 = Range("P18")
Set tcell2 = Range("P21")

If (Range("P18").Value < ucell1.Value Or Range("P18") = ucell1.Value) _
And (Range("P18").Value > lcell1.Value Or Range("P18").Value = lcell1.Value) Then
Range("P18").Interior.ColorIndex = 29
Range("P18").FormatConditions.Delete
ElseIf Range("P18").Value = lcell1.Value Then
Range("P18").Interior.ColorIndex = 29
Range("P18").FormatConditions.Delete
ElseIf Range("P18").Value > ucell1.Value Or Range("P18").Value < lcell1.Value Then
Range("P18").Interior.ColorIndex = 3
Range("P18").FormatConditions.Delete
End If

End Sub
 
G

Guest

Hi Bernie,

Thanks for the reply and sorry to bother you with this again. Yes the code
does contain another part for cell P21 but I have shorten my codes for this
post. I have tried the code you suggested but the problem still remain. I
hvae also tested the following codes and the results are explained after the
codes (I have only use cell P18 as example in the code):
_________________________________________
Test 1: If cell P18 = 0 (Cell S18), cell P18 is Green

If Range("P18") = Range("S18") Then
Range("P18").FormatConditions.Delete
Range("P18").Interior.ColorIndex = 29 'green color

End If

Result: cell P18 stay in the previous color and did not change to green
__________________________________________________________
Test 2: If cell P18 is between R18 and S18 (or 1 and 0) then P18 is green,
else it is red

If (Range("P18") <= Range("R18")) And (Range("P18") >= Range("S18")) Then
Range("P18").FormatConditions.Delete
Range("P18").Interior.ColorIndex = 29

Else
Range("P18").FormatConditions.Delete
Range("P18").Interior.ColorIndex = 3
End If

Result: All conditions are met except when P18 = zero, it will stay as
previous color and not green
________________________________________________________________
Test 3: If cell P18 is between R18 and S18 (or 1 and 0) then P18 is green,
else if P18 = 0 then it is also green

If (Range("P18") <= Range("R18")) And (Range("P18") >= Range("S18")) Then
Range("P18").FormatConditions.Delete
Range("P18").Interior.ColorIndex = 29

ElseIf Range("P18") = Range("S18") Then
Range("P18").FormatConditions.Delete
Range("P18").Interior.ColorIndex = 29
End If

Result: All conditions are met and P18 turned green, values > 1 and < 0 also
became green.
________________________________________________________________

Test 4: Same with above except added code to set red color to cell > 1 and < 0

If (Range("P18") <= Range("R18")) And (Range("P18") >= Range("S18")) Then

Range("P18").Interior.ColorIndex = 29
Range("P18").FormatConditions.Delete

ElseIf Range("P18") = Range("S18") Then

Range("P18").Interior.ColorIndex = 29
Range("P18").FormatConditions.Delete

ElseIf Range("P18") > Range("R18") Or Range("P18") < Range("S18") Then
Range("P18").FormatConditions.Delete
Range("P18").Interior.ColorIndex = 3

End If

Result: All conditions are met except when P18=0, it stays as previous color
and not green.
_______________________________________________________

Any help and advice will be much appreciated!

Many thanks

Best Regards
Casey Wong
 
B

Bernie Deitrick

Casey,

Try this:
Test 1: If cell P18 = 0 (Cell S18), cell P18 is Green
Click to expand...

Range("P18").Value = Range("S18").Value
If Range("P18").Value = Range("S18").Value Then
MsgBox "I am now reformatting P18"
Range("P18").FormatConditions.Delete
Range("P18").Interior.ColorIndex = 29 'green color
End If


For me, 29 is not green - it is purple (at least on my system, XL2002).

What happens? Then try this, to try a different color:

Range("P18").Value = Range("S18").Value
If Range("P18").Value = Range("S18").Value Then
MsgBox "I am now reformatting P18"
Range("P18").FormatConditions.Delete
Range("P18").Interior.ColorIndex = 3 'RED
End If

HTH,
Bernie
MS Excel MVP
 
G

Guest

Hi Bernie,

I have run the code you have suggested and the cell P18 changed to Green
after the first code and to Red after the second code.....what should be my
next step?

Best Regards
Casey
 
B

Bernie Deitrick

Casey,

That proves that the code works as it should.

The next step is making sure that you are actually coding for what you want - are the conditional
comparisons correct (you may format a cell to show an interger, and think the value is 1, but it is
really .9995, so the comparison fails), are the colors correct (is 29 green or purple?), is your
logic correct (can the two parts of your AND actually happen at the same time), etc.

Put msgbox statements throughout your code (at least one in each part of every IF Then Else
structure), similar to my example, then enter values into the cells that you think will trigger the
condition, and see what happens.

HTH,
Bernie
MS Excel MVP
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Excel VBA 1
VBA Excel - Select a value from one cell in Excel to update a SQL table 1
Trying to run excel vba code when cell value changes from empty to entered value. 1
copy cell color but not conditional format equations 2
SUMPRODUCT and SUMIFS combined with Sumifbycolor (VBA code) 1
Conditional Formatting: Data Bar, change gradient color based oncondition? 1
change certain cell colours in VBA 2
Changing cell color from a function 2

Top