Coloring cells VBA

[FadDak]

I am currently working on a project in VBA Excel, and I've encountered an issue with dynamically changing the color of cells based on a numerical threshold. The specific challenge is that I want cells in column A to turn green if the value is above 100,000 and red if it's 100,000 or below. Despite my attempts, I haven't been able to get the VBA code to respond correctly to these conditions.

Thank you in advance for your assistance.

 

[Joe4]

Welcome to the Board!

You don't need VBA to do this, it can be done pretty easily with Conditional Formatting.
See: Use conditional formatting to highlight information - Microsoft Support

Post back to this thread with any specific questions about it.

 

[dreid1011]

Maybe something like this:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not Intersect(Target, Range("A1:A100")) Is Nothing Then
    Select Case Target.Value
        Case Is > 100000
            Target.Interior.Color = vbGreen
        Case Is <= 100000
            Target.Interior.Color = vbRed
    End Select
End If
Application.EnableEvents = True
End Sub

 

[Joe4]

As dreid1011 showed, it is not hard to do via VBA. All things equal, there are usually a few good reasons to use Conditional Formatting over VBA for something like this:
1. Why use VBA to create built-in Excel functionality that already exists?
2. Code will only run if VBA is enabled for the workbook (and VBA cannot be run in web version of Excel)
3. Related to 2 above, many users will not enable VBA for security concerns (or workplace security forbids it)

Perhaps there are good reasons for wanting to use VBA, i.e. maybe it is part of a larger VBA project.

 

[dreid1011]

Maybe something like this:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not Intersect(Target, Range("A1:A100")) Is Nothing Then
    Select Case Target.Value
        Case Is > 100000
            Target.Interior.Color = vbGreen
        Case Is <= 100000
            Target.Interior.Color = vbRed
    End Select
End If
Application.EnableEvents = True
End Sub

Added a line to account for blank values after already having had a value.

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not Intersect(Target, Range("A1:A100")) Is Nothing Then
    Select Case Target.Value
        Case ""
            Target.Interior.Color = xlNone
        Case Is > 100000
            Target.Interior.Color = vbGreen
        Case Is <= 100000
            Target.Interior.Color = vbRed
    End Select
End If
Application.EnableEvents = True
End Sub