jeudi 18 février 2021

VBA - Cell interior color based on cell value - compile error - Object required

I am trying to implement below code into one of modules, but it comes with "Object required" error pointing at (Set NegValue = "FALSE") Not sure what is wrong. I have columns "I" & "M" populated with values "TRUE", "FALSE", "#N/A" from row 3 down. Depense on exports i am using to compare them, amount of row might change. I need this code to change cell interior color based on cell values up to last row. What i am doing wrong?

Dim PosValue As Variant, NegValue As Variant, NoValue As Variant
Dim Cell2 As Range, TempRange As Range
Dim lr As Long, r As Long, Count As Long, sh As Worksheet

Set sh = ThisWorkbook.ActiveSheet
Set NegValue = "FALSE"
Set PosValue = "TRUE"
Set NoValue = "#N/A"
Set TempRange = Range("I:M")

Set lr = sh.Cells(Rows.Count, 1).End(xlUp).Row

For Count = 3 To lr

For Each Cell2 In TempRange
If Cell2.Value = PosValue Then _
Cell2.Interior.Color = vbGreen
If Cell2.Value = NegValue Then _
Cell2.Interior.Color = vbRed
If Cell2.Value = NoValue Then _
Cell2.Interior.Color = vbBlue
Next Cell2
Next Count

So after changes code will look like below. But still comes with an error. This time error 13.

Dim PosValue As String, NegValue As String, NoValue As String
Dim Cell2 As Range, TempRange As Range
Dim lr As Long, r As Long, Count As Long, sh As Worksheet

Set sh = ThisWorkbook.ActiveSheet
Set TempRange = Range("I:M")
NegValue = "FALSE"
PosValue = "TRUE"
NoValue = "#N/A"

lr = sh.Cells(Rows.Count, 1).End(xlUp).Row

For Count = 3 To lr

For Each Cell2 In TempRange
If Cell2.Value = PosValue Then _
Cell2.Interior.Color = vbGreen
If Cell2.Value = NegValue Then _
Cell2.Interior.Color = vbRed
If Cell2.Value = NoValue Then _
Cell2.Interior.Color = vbBlue
Next Cell2
Next Count

Aucun commentaire:

Enregistrer un commentaire