Changing Color based on value

Question

I'm making a form with three buttons on it and a small spreadsheet of data. Each button is going to correspond with a different range of acceptable results. When you push the first button, I want it to highlight the results in the sheet as green if they are between 38 and 44.4, and highlight results out of that range in red. For the second button, I'd like the good range to be 33 to 39.4, and the third button to be 33 to 39.4 (same acceptable results range, different type of test). Basically I need different conditional formatting to be enacted with each button. I'm currently using:

Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Range("C16") > 44.4 Then Target.Interior.Color = vbRed
If Target.Range("C16") < 38 Then Target.Interior.Color = vbRed
If Target.Range("C16") >= 38 And Target <= 44.4 Then Target.Interior.Color = vbGreen


End Sub

I'm not familiar with the selection change property, and this doesn't work anyway. Not sure where to go from here. Any help would be appreciated. Thanks!

*SelectionChange* isn't a *property*, it's a `Worksheet` **event**. `Worksheet_SelectionChange` *handles* that event when it's raised/fired: it's an *event handler* procedure. Side note, event handlers should be `Private`, yours is implicitly `Public`. — Mathieu Guindon, Feb 20 '17 at 16:50
@Mat'sMug - If the Worksheet_SelectionChange is in a private worksheet code sheet (and that's the only place it is going to trigger) and not a public module code sheet then it is implicitly private. — , Feb 20 '17 at 16:53
@Jeeped [hmm, well that's not what the object browser sees](https://i.stack.imgur.com/CrlPy.png). — Mathieu Guindon, Feb 20 '17 at 16:56
@Mat'sMug - Hmmm, well I know you cannot call it from another codesheet. It must be public to that codesheet but private beyond that. — , Feb 20 '17 at 17:01
Yeah I'm steering away from the event. I need something less encompassing like the code in the next answer. — ladymrt, Feb 20 '17 at 17:15
@Jeeped If I have that handler in `Sheet1` and make it say `Debug.Print "Hello"`, and then go to, say, `Sheet2`, and expose a `Public Sub Test()` that does `Sheet1.Worksheet_Change Range("A1")`, I get "Hello" printed to the immediate pane. [Seems Public means "public" after all](https://i.stack.imgur.com/Hdm7u.png) ;-) — Mathieu Guindon, Feb 20 '17 at 17:44

Mathieu Guindon · Answer 1 · 2017-02-20T18:02:54.980

44.4 is greater or equal to 38, and the check for it is running regardless of the outcome of the other conditions, meaning any value greater than or equal to 38 is going to be green.

You need to better specify your conditions, specifically, the "between X and Y" part. This looks like a job for a Select Case block - for example this would be the "green between 38 and 44.4" code:

Select Case ActiveSheet.Cells("C16").Value
    Case 38 To 44.4
       Target.Interior.Color = vbGreen
    Case Else
       Target.Interior.Color = vbRed
End Select

The "green between 33 and 39.4" code would look like this:

Select Case ActiveSheet.Cells("C16").Value
    Case 33 To 39.4
       Target.Interior.Color = vbGreen
    Case Else
       Target.Interior.Color = vbRed
End Select

Notice a pattern? Parameterize it and put it in its own procedure:

Public Sub ConditionalHighlight(ByVal checkCell As Range, ByVal target As Range, ByVal lowerLimit As Double, ByVal upperLimit As Double)
    'ensure the value is a Double (and not an error or a string):
    If VarType(checkCell.Value) <> vbDouble Then Exit Sub

    'work with an actual Double instead of the Variant we get off the cell:
    Dim checkValue As Double
    checkValue = CDbl(checkCell.Value)

    Select Case checkValue
        Case lowerLimit To upperLimit
            target.Interior.Color = vbGreen
        Case Else
            target.Interior.Color = vbRed
    End Select

End Sub

Now just call that from your buttons' Click handlers:

Private Sub Button1_Click()
    ConditionalHighlight Range("C16"), Range("whatever"), 38, 44.4
End Sub

Private Sub Button2_Click()
    ConditionalHighlight Range("C16"), Range("whatever"), 33, 39.4
End Sub

Private Sub Button3_Click()
    ConditionalHighlight Range("C16"), Range("whatever"), 42, 74.4
End Sub

@Jeeped thanks, fixed. I hate that confusing `Case Is` syntax anyway :) — Mathieu Guindon, Feb 20 '17 at 17:32

pokemon_Man · Answer 2 · 2017-02-20T18:23:14.603

-1

Put in a button:

Sub Button1()

    Dim myRange, cell As Range
    Set myRange = Range("C16:G16")

    For Each cell In myRange
       If cell.Value > 44.4 Then
          cell.Interior.Color = vbRed
       ElseIf cell.Value < 38 Then
          cell.Interior.Color = vbRed
       ElseIf cell.Value >= 38 And cell.Value <= 44.4 Then
          cell.Interior.Color = vbGreen
       End If
    Next
End Sub

edited Feb 20 '17 at 18:23

answered Feb 20 '17 at 16:44

pokemon_Man

902
1
9
25

That works great, but when I change the range to ("C16:G16") it gives me a run-time error. Is there a different syntax for a range of cells? Or any idea why the range won't work? Worst come to worst I can copy/paste this code five times for each cell... – ladymrt Feb 20 '17 at 17:14
You will need to specify your range in the "Set myRange = Range("YourRange). see updated post. – pokemon_Man Feb 20 '17 at 17:22
1

Use `Select Case` or `ElseIf` so you are not comparing values that have already met one criteria. – Feb 20 '17 at 17:31

Changing Color based on value

2 Answers2