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

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

"Naji" <(E-Mail Removed)> wrote in message
news:8c90d81a-9aa8-4fbe-9588-(E-Mail Removed)...
> 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