Interior Cell Color in Excel 2

[Tronsliver]

I'm struggling with this, hopefully someone can tell me how to make this work. I'm trying to color the interior of a cell based on the value of that cell. Here is the procedure:

Public Sub InteriorCells()
Dim x As Long

For x = 3 To 21
If Cells(x, "D&quot.Value >= 90 Then
Selection.Interior.ColorIndex = 4
Else
If Cells(x, "D&quot.Value > 80 Then
Selection.Interior.ColorIndex = 6

End If
End If

Next x

End Sub

 

[JESTAR]

Hi, not sure if it will help, but could you use elseif?

For x = 3 To 21
If Cells(x, "D&quot.Value >= 90 Then
Selection.Interior.ColorIndex = 4
ElseIf Cells(x, "D&quot.Value > 80 Then
Selection.Interior.ColorIndex = 6

End If

Next x

End Sub

 

[Tronsliver]

JESTAR,

I tried your solution and received a run time error 1004.
It says "Unable to set colorindexproperty of the interior class....Help

 

[JESTAR]

Hi again.

Try using a "with" statement. something like this...

For x = 3 To 21
If Cells(x, "D&quot.Value >= 90 Then
With Selection.Interior
.ColorIndex = 4
.Pattern = xlSolid
End With
ElseIf Cells(x, "D&quot.Value > 80 Then
With Selection.Interior
.ColorIndex = 6
.Patter = xlSolid
End With
End If

Next x

 

[acron]

Try

For x = 3 To 21
  If Cells(x, "D").Value >= 90 Then
      Cells(x, "D").Interior.ColorIndex = 4
   Else  If Cells(x, "D").Value > 80 Then
      Selection.Interior.ColorIndex = 6

or

For x = 3 To 21
  If Cells(x, "D").Value >= 90 Then
      x = 4
   Else  If Cells(x, "D").Value > 80 Then
      x =
   end if
   (x, "D").Interior.ColorIndex = x

where x is a variable to which you assign the colorindex.

Have yopu considered Conditional Formatting ?

AC

 

[JESTAR]

Oops, typo in one of my lines. *Pattern*

 

[dsb]

If you are going to use

Selection.Interior.ColorIndex

to color the cell, then you need to select this cell explicitly. Merely using it (Cells(x,"D&quot.value) is not enough

For x = 3 to 21
Cells(x,"D&quot.Select
With Selection
Select case .value
Case >= 90: .interior.colorindex=4
Case > 80: .interior.colorindex = 6
case else
'do nothing
end select
end with
Next X


DSB

 

[Tronsliver]

Thanks everyone for your help and DSB thank you for the solution you gave it worked perfectly in my application.

Tronsliver

 

[acron]

dsb,

Not taking anything away from your solution, but I must disagree with <to color the cell, then you need to select this cell explicitly>.

There is rarely ever a need to select a range using VBA in order to manipulate it. The following WILL work :

For x = 3 To 21
   With Cells(x, "D")
       Select Case .Value
         Case Is >= 90: .Interior.ColorIndex = 4
         Case Is > 80: .Interior.ColorIndex = 6
         Case Else
           'do nothing
      End Select
   End With
Next x

In fact it bears a striking resemblance to code already posted in a different thread on the same subject a few days ago.

AC

 

[dsb]

Right on! There is no need to SELECT the cell before setting it, but I had presumed that the author had
a reason for using a SELECTION in the example and responded in spirit.

dsb

 

[Loomah]

This thread has confused me (but I must admit to learning somethingfrom it!)

Unless you're using Excel v7 or earlier, why not just use conditional formatting on the relevant cells?

 

[Tronsliver]

Looma,

Although helpful in some situations, the conditional formating function is limited in its capability. If you need a varity of crteria VB is the way to go. My opinion!

 

[Loomah]

Tronsliver
While not disagreeing with you, after years of not having conditional formatting ie working in XL7 and having to do it by code, the new toy was a god send!!
Admitedly limited to 3 criteria but we're only talking 2 here

 

[Tronsliver]

Loomah,

I agree it is a nice feature, however, although the example I presented only showed 2 criteria the actual program needs many more.

Tronsliver

 

[dsb]

A little tidbit of assumed knowledge is that the Select Case statement tests the cases top-to-bottom and stops when it on the first case that passes.

because of this, it was important in the example to do
Case >=90
Case > 80
rather than
Case > 80
Case >=90

dsb