Creating a Macro For Shading Rows - Or Should I Use Conditional Formatting?
 

I have a spreadsheet that I need to shade. Usually I do this manually,
but it's 3000 lines long, and doing this every time I run the report
is extremely time consuming.

Basically, the spreadsheet is listing the On Hand units and Sold Units
for different styles of belts by sizes. It looks something like this:

Style ID Size Total
8C0303 80 OH 0
8C0303 80 Sold 0
8C0303 80 OH 3
8C0303 80 Sold 0
8C0303 85 OH 0
8C0303 85 Sold 0
8C0303 85 OH 5
8C0303 85 Sold 2
8C0303 90 OH 0
8C0303 90 Sold 0
8C0303 95 OH 0
8C0303 95 Sold 0
8C0303 OH 8
8C0303 Sold 2
total oh 16
total sold 4

I would like to Shade all rows that show OH units so that the
spreadsheet is easier to read. Any ideas? Thanks so much for your
help!

Best,
Danielle

Creating a Macro For Shading Rows - Or Should I Use Conditional F
 

hi,
try this
Sub MacDoColor()
Dim r1 As Range
Dim r2 As Range
Sheets("sheet1").Select
Set r1 = Cells(2, 1)
Do
Set r2 = r1.Offset(1, 0)
If r1.Offset(0, 2).Value = "OH" Then
Range(r1, r1.Offset(0, 3)). _
Interior.ColorIndex = 6 'yellow
End If
Set r1 = r2
Loop While Not IsEmpty(r1)
MsgBox (" Done!")
End Sub

see PatternColorIndex property in VB help for other colors.

regards
FSt1

" wrote:

I have a spreadsheet that I need to shade. Usually I do this manually,
but it's 3000 lines long, and doing this every time I run the report
is extremely time consuming.

Basically, the spreadsheet is listing the On Hand units and Sold Units
for different styles of belts by sizes. It looks something like this:

Style ID Size Total
8C0303 80 OH 0
8C0303 80 Sold 0
8C0303 80 OH 3
8C0303 80 Sold 0
8C0303 85 OH 0
8C0303 85 Sold 0
8C0303 85 OH 5
8C0303 85 Sold 2
8C0303 90 OH 0
8C0303 90 Sold 0
8C0303 95 OH 0
8C0303 95 Sold 0
8C0303 OH 8
8C0303 Sold 2
total oh 16
total sold 4

I would like to Shade all rows that show OH units so that the
spreadsheet is easier to read. Any ideas? Thanks so much for your
help!

Best,
Danielle

Creating a Macro For Shading Rows - Or Should I Use Conditional Formatting?
 

Sub foo()
Dim wks As Worksheet
Set wks = ActiveSheet
iLastRow = wks.Cells(Rows.Count, 3).End(xlUp).Row
Application.ScreenUpdating = False
For i = iLastRow To 1 Step -1
If wks.Cells(i, 3).Value = "OH" Then
wks.Rows(i).EntireRow.Interior.ColorIndex = 6
End If
Next i
Application.ScreenUpdating = True
End Sub

Assumes that OH will appear in column C


Gord Dibben MS Excel MVP

On 3 May 2007 14:45:46 -0700, wrote:

I have a spreadsheet that I need to shade. Usually I do this manually,
but it's 3000 lines long, and doing this every time I run the report
is extremely time consuming.

Basically, the spreadsheet is listing the On Hand units and Sold Units
for different styles of belts by sizes. It looks something like this:

Style ID Size Total
8C0303 80 OH 0
8C0303 80 Sold 0
8C0303 80 OH 3
8C0303 80 Sold 0
8C0303 85 OH 0
8C0303 85 Sold 0
8C0303 85 OH 5
8C0303 85 Sold 2
8C0303 90 OH 0
8C0303 90 Sold 0
8C0303 95 OH 0
8C0303 95 Sold 0
8C0303 OH 8
8C0303 Sold 2
total oh 16
total sold 4

I would like to Shade all rows that show OH units so that the
spreadsheet is easier to read. Any ideas? Thanks so much for your
help!

Best,
Danielle

Creating a Macro For Shading Rows - Or Should I Use Condition
 

Hi Danielle
If you can't handle a macro
You could also use conditional format.
Highlight all the used cells in the first OH row.
Go to Conditional Format, Select Formula Is
Put in =$B3="OH" and format to taste. Change cell to whatever you need to use.
When finished copy cells down as far as required.
easy way to do this is to double click on the little box in the bottom right
corner of the first cell and it will ppopulate down to the last cell.

HTH
Michael M

"FSt1" wrote:

hi,
try this
Sub MacDoColor()
Dim r1 As Range
Dim r2 As Range
Sheets("sheet1").Select
Set r1 = Cells(2, 1)
Do
Set r2 = r1.Offset(1, 0)
If r1.Offset(0, 2).Value = "OH" Then
Range(r1, r1.Offset(0, 3)). _
Interior.ColorIndex = 6 'yellow
End If
Set r1 = r2
Loop While Not IsEmpty(r1)
MsgBox (" Done!")
End Sub

see PatternColorIndex property in VB help for other colors.

regards
FSt1

" wrote:

I have a spreadsheet that I need to shade. Usually I do this manually,
but it's 3000 lines long, and doing this every time I run the report
is extremely time consuming.

Basically, the spreadsheet is listing the On Hand units and Sold Units
for different styles of belts by sizes. It looks something like this:

Style ID Size Total
8C0303 80 OH 0
8C0303 80 Sold 0
8C0303 80 OH 3
8C0303 80 Sold 0
8C0303 85 OH 0
8C0303 85 Sold 0
8C0303 85 OH 5
8C0303 85 Sold 2
8C0303 90 OH 0
8C0303 90 Sold 0
8C0303 95 OH 0
8C0303 95 Sold 0
8C0303 OH 8
8C0303 Sold 2
total oh 16
total sold 4

I would like to Shade all rows that show OH units so that the
spreadsheet is easier to read. Any ideas? Thanks so much for your
help!

Best,
Danielle