Highlight table rows different color for every change in word

gertie193

Board Regular
Joined
Feb 28, 2008
Messages
82
Hello,

I am using Excel 2007.

I have a big spreadsheet that has been sorted by column A, which is a list of product types--the words can vary, so there are no set words I can give you.

I don't know how to write code, but I would like a VBA code that will highlight a table row (not an entire row) a certain pastel color for all products that have the same word in column A. When the word changes in column A to the next set of product types, then I want Excel to highlight these sets of rows a little darker version of the previous color. When the word changes in column A to yet another set of product types, I want Excel to highlight these sets of rows back to the original pastel color...and so on, so that the table rows are highlighted by product types, alternating btw. the two colors.

I couldn't find this anywhere. Can anyone help?

Thank you in advance for your help! :)
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
Hello,

I am using Excel 2007.

I have a big spreadsheet that has been sorted by column A, which is a list of product types--the words can vary, so there are no set words I can give you.

I don't know how to write code, but I would like a VBA code that will highlight a table row (not an entire row) a certain pastel color for all products that have the same word in column A. When the word changes in column A to the next set of product types, then I want Excel to highlight these sets of rows a little darker version of the previous color. When the word changes in column A to yet another set of product types, I want Excel to highlight these sets of rows back to the original pastel color...and so on, so that the table rows are highlighted by product types, alternating btw. the two colors.

I couldn't find this anywhere. Can anyone help?

Thank you in advance for your help! :)
Here's some code that will need tweaking to fit your needs. Here I assumed the table starts in cell A1, and has no blank rows. You can adjust the values of R1,G1,B1 and R2,G2,B2 to give you the pastel shades you want.
Code:
Sub TwoColorBands()
Dim rng As Range

Dim lastCol As Long, lastRw As Long, indx As Long, ct As Long
Dim R1 As Integer, G1 As Integer, B1 As Integer
Dim R2 As Integer, G2 As Integer, B2 As Integer

lastCol = Cells(1, Columns.Count).End(xlToLeft).Column
lastRw = Range("A" & Rows.Count).End(xlUp).Row
R1 = 200
G1 = 50
B1 = 100
R2 = 250
G2 = 75
B2 = 120
indx = 0
Set rng = Range("A1", Cells(lastRw, lastCol))
rng.Rows(1).Interior.Color = RGB(R1, G1, B1)
For Each rw In rng.Rows
    If rw.Cells(1).Value = rw.Cells(1).Offset(1, 0).Value Then
        rw.Offset(1, 0).Interior.Color = rw.Interior.Color
    ElseIf rw.Offset(1, 0).Row <= lastRw Then
        indx = indx + 1
        If indx Mod 2 <> 0 Then
            rw.Offset(1, 0).Interior.Color = RGB(R2, G2, B2)
        Else
           rw.Offset(1, 0).Interior.Color = RGB(R1, G1, B1)
        End If
    End If
Next rw
End Sub
 
Upvote 0
JoeMo,

That worked perfectly!!! Thank you so much for answering my question.
You have helped me tremendously at work.

All the best.

Sincerely,
gertie193
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,943
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top