Ranges in Excel

[bernie10]

Hello all,

I need some help manipulating some ranges in excel. I have a spreadsheet that I am creating from an Access form that needs some color-coding- the code I am using is as follows:

For Each c In DataSht.UsedRange
    If c.Text = "Manual" Then c.Interior.Color = RGB(255, 255, 0)
    If c.Text = "Open" Then c.Interior.Color = RGB(217, 207, 251)
Next c

By using the UsedRange property though, obviously I am searching through all of the columns on the spreadsheet (12). In actuality the only ones that need to be searched and color-coded are columns D, G, H, and I. Though the process I've got right now works, I think I am slowing the process way down by making it search the entire used range. How can I re-write this so it only searches the four columns of interest.

Thanks in advance for the help.

CM

 

[Zathras]

Here is one way:

For Each c In Intersect(DataSht.UsedRange, _
              Union(DataSht.Range("D:D"), _
                    DataSht.Range("G:I")))
    If c.Text = "Manual" Then c.Interior.Color = RGB(255, 255, 0)
    If c.Text = "Open" Then c.Interior.Color = RGB(217, 207, 251)
Next c

 

[Ravalia]

Can it not be done simply using Conditional Formatting?

 

[bernie10]

Thanks for both of the replies.

Zathras, your way worked well.

Ravalia, I'm sure you probably can but I am not too familar with conditional formatting.

 

[PHV]

The conditional formatting way:
With DataSht.Range("D,G:I")
.FormatConditions.Delete
.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="=""Open"""
.FormatConditions(1).Interior.Color = RGB(217, 207, 251)
.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="=""Manual"""
.FormatConditions(2).Interior.Color = RGB(255, 255, 0)
End With

If you use late binding: xlCellValue=1 and xlEqual=3

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244