macro to shade cell

Posted by Trevor on January 13, 2002 10:27 PM

Hi,
I have a five column list which is sourced from access.
column B, is numeric and in the order 100 to 9900, but not all numbers are used.
eg 100 to 135, then 200 to 256, 300 to 312. etc
The numbers used often change.
I would like to have a macro so that each time the list is refreshed from access all the rows which have the hundred multiple (100,200,300)
would be shaded, say light grey (for cols A,B,C,D,E)
that is one cell to the left of the hundred series, the cell itself and three cells to the right.

TIA Trevor
.

Posted by Jacob on January 14, 2002 4:51 AM

Conditional formatting will work:

For column a:d put formula is =if(or(b1=100,b1=200,b1=300,b1=400,b1=500),true,false)
Then select the formatting you want then fill the formatts down.

HTH

Jacob

Posted by Eli Weiss on January 14, 2002 6:28 AM

Trevor, try this:

Sub MyMacro()
Dim MyRange As Range
Dim i, va As Integer
Range("A1").Select
Set MyRange = ActiveCell.CurrentRegion
For i = 1 To MyRange.Rows.Count
va = MyRange(i, 2).Value
If va Mod 100 = 0 Then
MyRange.Rows(i).Interior.Color = vbYellow
End If
Next i
End Sub

Eli