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
In article
<1a496a49-df04-458b-a1cc-(E-Mail Removed)>,
(E-Mail Removed) wrote:
> 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
Similar Threads
