changing the fill color on a cell

[jseger22]

Hi,

I am trying to fill the color of a cell based on if statements, but I
don't know how to reference the cell. Everything works except when I
try to reference the CellValue to fill the cell and I am not sure what
I am doing wrong because I am only a novice at VBA. My code looks
like

Sub Macro2()
Dim lastyear As Double
Dim target As Double
Dim CellValue As Double
Dim i As Integer

For i = 5 To 15

CellValue = Cells(i, 37)
target = Cells(i, 3) * 100
lastyear = Cells(i, 25)


If CellValue >= lastyear And CellValue >= target Then
CellValue.Interior.ColorIndex = 4
ElseIf CellValue >= lastyear And CellValue < target Then
CellValue.Interior.ColorIndex = 3
ElseIf CellValue < lastyear And CellValue < target Then
CellValue.Interior.ColorIndex = 6
Else
CellValue.Interior.ColorIndex = 3
End If
Next
End Sub

 

[JE McGimpsey]

CellValue is declared as type Double, but you're trying to use it as a
range object.

One possibility:

Dim lastyear As Double
Dim target As Double
Dim CellValue As Double
Dim i As Integer

For i = 5 To 15

CellValue = Cells(i, 37).Value
target = Cells(i, 3).Value * 100
lastyear = Cells(i, 25).Value


If CellValue >= lastyear And CellValue >= target Then
Cells(i, 37).Interior.ColorIndex = 4
ElseIf CellValue >= lastyear And CellValue < target Then
Cells(i, 37).Interior.ColorIndex = 3
ElseIf CellValue < lastyear And CellValue < target Then
Cells(i, 37).Interior.ColorIndex = 6
Else
Cells(i, 37).Interior.ColorIndex = 3
End If
Next i


A somewhat more efficient way would be:

Dim lastyear As Double
Dim target As Double
Dim i As Integer
Dim nCI As Long

For i = 5 To 15
target = Cells(i, 3).Value * 100
lastyear = Cells(i, 25).Value

With Cells(i, 37)
nCI = 3
If .Value >= lastyear Then
nCI = nCI - (.Value >= target)
Else
nCI = nCI - 2 * (.Value < target)
End If
.Interior.ColorIndex = nCI
End With
Next i