CODEWITHSUNDEEP
excelvbacolorsconditional-formattingselectionchanged
ladymrt
ladymrt

Reputation: 95

Changing Color based on value

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!

Upvotes: 0

Views: 355

Answers (2)

pokemon_Man
pokemon_Man

Reputation: 902

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

Upvotes: -1

Mathieu Guindon
Mathieu Guindon

Reputation: 71167

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

Upvotes: 4

Related Questions