Highlighting a Cell, based on value / blank.

[robert_shindorf]

Hi folks..

I have a macro, and it mainly works, except the second part where it checks if the value is less than 7, and returns it to a white background.

The cell has a formula in it that returns "" based on another series of cells. I have considered changing that to a zero and formating, but for reasons that don't need explanation, i have chosen not to.

here is the macro. I need the second part to be activated if less than 7, as well as if the cell returns ""

Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
Dim lrow As Long
Dim i As Integer
Dim WSRec As Worksheet
Set WSRec = Sheets("Payables")
lrow = WSRec.Range("f65536").End(xlUp).Row
For i = 13 To lrow
    If WSRec.Cells(i, 10).Value > 7 Then
        WSRec.Cells(i, 10).Select
        With Selection.Font
            .FontStyle = "Bold"
        End With
        With Selection.Interior
          .Color = 255
        End With
    End If
        If WSRec.Cells(i, 10).Value < 7 Then
        WSRec.Cells(i, 10).Select
        With Selection.Font
            .FontStyle = "Regular"
        End With
        With Selection.Interior
          .Color = RGB(255, 255, 255)
        End With
    End If
Next
End Sub

Thanks in advance.

[Richard Buttrey]

Hi,

Perhaps

If WSRec.Cells(i, 10).Value < 7 OR WSRec.Cells(i, 10).Value ="" Then

HTH

[robert_shindorf]

ahhh ha!

I was trying to write it like a formula or(condition1, consition2)

haha, thanks!

[robert_shindorf]

woops, looks like one more thing is happening, when the workbook changes, it moves the selected cell to column 10, is there something i can add to the end to keep the selection where it is?

[Richard Buttrey]

woops, looks like one more thing is happening, when the workbook changes, it moves the selected cell to column 10, is there something i can add to the end to keep the selection where it is?

Hi,

Assuming you're talking about column F being the active colum,

Range("F" & lRow).Select

But I can't help thinking you're better not selecting column 10 in the first place. Try and avoid .Select and .Activate.

Try just

With WSRec.Cells(i, 10)
  .Font.FontStyle = "Bold"
  .Interior.Color = 255
 End With

HTH

[robert_shindorf]

you were absolutely right... avoiding selecting that cell was better a better bet.

thanks again.