Only color every third row based on a value in another sheet.

RobBan67

New Member
Joined
Jan 23, 2021
Messages
6
Office Version
  1. 365
Platform
  1. Windows
Hello everyone.
First of all, I’m a VBA rookie, so please bare with me :)
This is what I’m trying to do.
I have one sheet (Project List) with a table (Table1) that looks like this.

Project List.png


What I’m trying to achieve is to set a specific background color to every third row
based on the color Index in another Sheet called (Data), that l looks like this.

Data.jpg

So, I want the script to search for the “customer” name in the sheet Data and return the right color for each Customer to the Project List color the with the right color.

I found this code online that I tried to modify to fit my needs, but without succsess.
Right now the script only color the first two “Customers” but color the both of them with the color of the first customer (Walker & Son).

I hope I can get some help and guidance to make this work, because this is beyond my knowledge 🙏
Thanks!

VBA Code:
Sub Color_Project()

Dim MyRange As Range

Dim RowSelect As Range

Dim SeekValue As Range

Dim ReturnValue As Range

Dim i As Integer


Set SeekValue = Sheets("Project List").Range("Table1[Customer]")

Set ReturnValue = Sheets("Data").Range("Data[Customer]")



Set MyRange = Sheets("Project List").Range("Table1")

Set RowSelect = MyRange.Rows(5)



For i = 1 To RowSelect.Rows.Count Step 4

Set RowSelect = Union(RowSelect, MyRange.Rows(i))



For Each Datacell In SeekValue

For Each ColorValueCell In ReturnValue



If Datacell.Value = ColorValueCell.Value Then

Set ColorIndexCell = Sheets("Data").Range("C" & ColorValueCell.Row)

RowSelect.Interior.ColorIndex = ColorIndexCell.Value


End If

Next

Next

Next

End Sub
 

RobBan67

New Member
Joined
Jan 23, 2021
Messages
6
Office Version
  1. 365
Platform
  1. Windows
In that case you are probably better off leaving the entire row coloured & then using conditional formatting to change the fill colour if it's ""
Okey, thanks for your help, its mush appreciated!
 

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,626
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,747
Messages
5,626,628
Members
416,195
Latest member
tonmcg

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
Top