VBA-highlight cell(s) in a column range if empty or greater than a value

I am using Excel 2010 (x32). I am new to VBA and trying to create some VBA conditional formatting to be used as data validation. The first column is a data column. I want to fill a cell red if it is empty or orange if the cell is greater than a value in cell B4 on another sheet named "Instructions". I have created the following but it is not working. Any suggestions? Also, instead of the named range of "A2:A2552", can I use a do until or do while loop to accomodate a growing range?

Code:

---

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim icolor As Integer
    Dim cell As Range

    If Intersect(Target, Range("A2:A2552")) Is Nothing Then Exit Sub

    For Each cell In Target
        icolor = 0
        Select Case cell
            Case Is > Range("Instructions!B4"): icolor = 3
            Case "": icolor = 45
        End Select

        If icolor <> 0 Then cell.Interior.ColorIndex = icolor
    Next cell
End Sub

---

Two options

Code:

---

Private Sub Worksheet_Change(ByVal Target As Range)
'Assumes all changes are by the user entering a value

If Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub 'Prevents running on simultaneous changes

With Target
  Select Case .Value
    Case Is > Sheets("Instructions").Range("B4")
      .Interior.ColorIndex = 3
    Case "": .Interior.ColorIndex = 45
  End Select
End With
End Sub

---

Code:

---

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cel As Range 'Cell is a keyword, do not use Keywords as variables
Dim DataRange As Range
Dim Maxx As Double
 
Set DataRange = Intersect(Target, Range("A:A"))
If DataRange Is Nothing Then Exit Sub

Maxx = Sheets("Instructions").Range("B4").Value
'Why look it up more than once

For Each Cel In DataRange
  With Cel
    Select Case .Value
      Case Is > Maxx
        .Interior.ColorIndex = 3
      Case "": .Interior.ColorIndex = 45
    End Select
  End With
Next Cel
End Sub

---

Thanks guys for the quick response. I have another question, sorry, I'm still quite new at this. When I try to run the macro, i do not see "Worksheet_Change" in the list of macros. I do not see any macros to run. I have placed this code in the "module1" and then tried "sheet1" without any luck It is almost like I created a function that needs to be called by a macro? Any advise, and again sorry for the confusion

Quote:

---

Originally Posted by mcinvalek

Thanks guys for the quick response. I have another question, sorry, I'm still quite new at this. When I try to run the macro, i do not see "Worksheet_Change" in the list of macros. I do not see any macros to run. I have placed this code in the "module1" and then tried "sheet1" without any luck It is almost like I created a function that needs to be called by a macro? Any advise, and again sorry for the confusion

---

From the editor side, double click 'Sheet1' (or Instructions) to open it's code module (sort of like a 'standard module' but contains sheet events). Private Sub Worksheet_Change(ByVal Target As Range) is a WS specific event, i.e. only to Sheet1, and it will run whenever there's a cell value change on Sheet1 (see the screen shot). The call HAS to look like that, and Target is the Range with the cell or cells that changed

If B4 on Instructions can change, and some of the col A values might change, you would probably need an event handler on both. The is a WB level, but 2 separate might be easier

Q: You said "some VBA conditional formatting to be used as data validation." There is an Excel conditional formatting (look in [Styles] group on the [Home] tab). Is that what you meant or just have VBA color the cell?

It it's the latter, then I think you'd need to handle cells that no longer meet the B4 criteria


Attachment 14783

Here's a small example WB that might give you ideas. It assumes that you really do want to color the cell and not use conditional formatting

Sam and Paul. I thank you for your assistance. Your corrections worked perfectly.

_keith