CODEWITHSUNDEEP
excelvba
Ingrid
Ingrid

Reputation: 15

EXCEL VBA changing background color based on value - rgb does not work

as the titles says I am looking for a way to change the color of cells in a certain range based on if they are lower or higher than the threshold criterion. Since I want to recoloring to occur every time the threshold value is changed this is my code so far but I keep getting an overflow exception on the line where I define the color codes based on rgb values 

Option Explicit    ' Force explicit variable declaration

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

Dim thresholdValue As Double
Dim resetColumnStart As Integer
Dim resetRowStart As Integer
Dim resetRowEnd As Integer
Dim ii, kk As Integer
Dim lightColor As Integer
Dim darkColor As Integer
Dim cellInRange As Excel.Range

thresholdValue = Sheets("ThresholdValues").Range(Cells(Target.Row, Target.Column), Cells(Target.Row, Target.Column))

If Target.Column = 2 Then
    If Target.Row = 3 Then
    resetColumnStart = 11
    lightColor = RGB(242, 221, 220)
    darkColor = RGB(217, 151, 149)
ElseIf Target.Row = 4 Then
    resetColumnStart = 18
    lightColor = RGB(219, 229, 241)
    darkColor = RGB(149, 179, 215)
Else
    Exit Sub
End If
Else
    Exit Sub
End If

resetRowStart = 3
For ii = 2 To 4 Step 1
Sheets(ii).Activate
resetRowEnd = Range("A65536").End(xlUp).Row
For kk = 1 To 7 Step 2
'reset the background colour for each data sheet
    With Range(Cells(resetRowStart, resetColumnStart + kk), Cells(resetRowEnd, kk))
        .Interior.Color = lightColor
        .Font.Color = RGB(0, 0, 0)
    End With
    With Range(Cells(resetRowStart, kk + 1), Cells(resetRowEnd, kk + 1))
        .Interior.Color = darkColor
        .Font.Color = RGB(255, 255, 255)
    End With
Next kk
Next ii


'color the values that are below the threshold for each data sheet
For ii = 2 To 4 Step 1
Sheets(ii).Activate
resetRowEnd = Range("A65536").End(xlUp).Row
currentRange = Range(Cells(resetRowStart, resetColumnStart), Cells(resetRowEnd, resetColumnStart + 6))
For Each cellInRange In currentRange
    If cellInRange.Value < thresholdValue Then
        cellInRange.Interior.Color = RGB(0, 255, 255)
        cellInRange.Font.Color = RGB(255, 0, 0)
    End If
Next cellInRange
Next ii

End Sub

I am completly new to VBA coding so it seems I am missing something essentialy which I hope you can help me with because coding this should be easy and I am loosing so much time on fancy things without doing the actual work

Upvotes: 1

Views: 1673

Answers (1)

SickDimension
SickDimension

Reputation: 912

One reason that your code cannot work, it the fact that Integer type cannot hold values as high as you are trying to pass. Integer has 16-bit boundaries meaning the highest and lowest values are -32,768 to 32,767. Now RGB(242, 221, 220) is actually value 14,474,738 that is out of the 16-bit boundary.

What you can do is change the following

Dim lightColor As Integer -> ... as Long
Dim darkColor As Integer -> ... as Long

Long variable has boundary -2,147,483,648 to 2,147,483,647 that should cover your need.

Upvotes: 2

Related Questions