Change background color based on cell value in vb?? Current code notworking...

[Naji]

Hello,

I'm having a hard time with this. I need to change the background
color for a cell if the number in B3 is equal to "1", but this code is
not working. The B3 value is based on a vlookup formula. This is what
I have:

Sub testme()
Dim myRng As Range
Dim myRow As Range
Dim myEmptyCells As Range

With Worksheets("PO Performance")
Set myRng = .Range("F3:X3")
End With

Set myEmptyCells = Nothing
On Error Resume Next
Set myEmptyCells = myRng.Cells.SpecialCells(xlCellTypeBlanks)
On Error GoTo 0


If myEmptyCells Is Nothing Then
MsgBox "no empties!"
Exit Sub
End If


For Each myRow In myRng.Rows
Set myEmptyCells = Nothing
On Error Resume Next
Set myEmptyCells = myRow.Cells.SpecialCells(xlCellTypeBlanks)
On Error GoTo 0
If myEmptyCells Is Nothing Then
'keep looking
Else
********************This is the part in question below.
The value in B3 is equal to one but *****************************this
code does not acknowledge that fact. What am I doing wrong?


myEmptyCells.Cells(1).Formula = "XXXXX"
If Range("B3").Value = 1 Then
myEmptyCells.Cells(1).Interior.ColorIndex = 3
End If
'stop looking
Exit For
End If
Next myRow

End Sub

 

[Bernie Deitrick]

Naji,

Be more specific about the location of cell B3:

If Range("B3").Value = 1 Then

Should be

If Worksheets("Sheetname").Range("B3").Value = 1 Then

You may also need to roung the value to 0 decimal places before doing the
comparison with 1, if the number being returned isn't truly 1 but just looks
that way (0.99999999999999997)

HTH,
Bernie
MS Excel MVP