Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cell shading with conditional formatting | Excel Discussion (Misc queries) | |||
Conditional formatting similar to shading alternating rows | Excel Worksheet Functions | |||
Conditional Formatting Shading For Visible Rows Only | Excel Discussion (Misc queries) | |||
Conditional formatting: alternate shading PLUS red for 0 values | Excel Discussion (Misc queries) | |||
conditional formatting-shading | Excel Worksheet Functions |