Macro to Change Row Color Based on Contents of Range

[cbsalt]

I have a workbook with 13 sheets. The first sheet is named Instructions, and subsequent sheets are Joe, Bob, Bill, Sue, Kate, Jud, Jen, Ron, Ann, Ian, Don, and John. I need a macro that will check the contents in cells B8:B109 in each of the sheets except Instructions, when the contents of one cell (Instructions!B15) changes. If any of the cells in B8:B109 is equal to any of the cell contents in E2:H11 on the Instructions sheet, I need to change the background color of the row(s) where a "match" occurs. When a "match" occurs, I would like the row color to change to lavender, which I believe is ColorIndex = 39.

Each of the cells in B8:B109 are formula driven, based on the contents of B15 on Instructions. B15 has 4 possible inputs (2000, 2050, 2500 and 3700), in addition to being empty. I need the row color to change back to "No Fill" if cell contents in B8:B109 no longer "match" any of the contents of Instructions!E2:H11.

One additional condition is that, when a "match" occurs, I also need cell H of each row to populate with specific text from Instructions. For example, if Joe!B19="end", I need Joe!H19 to say "Natural" and if Joe!B54="relax", I need Joe!H54="Natural", and if Joe!B98="down", I need Joe!H98="Client".

If there is no longer a "match," I need the contents of cell H to be cleared. I tried to attach a screenshot of the Instructions sheet, but I'm having issues with the uploader. So, a rough description of the sheet is below.

Column E
2000
end
top
down
start
stop
blue
due
plan
web
both

Column F
2050
client
for
ask
use
group
year
mail
door
pull
foot

Column G
2500
relax
turn
repeat
in
out
day
tube
stand
press
straight

Column H
3700
down
up
arch
heel
bear
flag
tank
tabs
hold
wall

Column I
Name
Natural
Customer
Client
Acess
National
International
Information
Service
Technology
Compare

Basically, if a cell in B8:B109 in any of the sheets matches any of the cell contents in Instructions!E2:H11, I want the H cell for the row that matches to populate with the corresponding contents of Column I. I'm not sure if it's important to note or not, but it is remotely possible that one cell on Joe will = "top" and one cell on Bob will = "top", but it is not possible that one cell on Joe will = "top" and one cell on Bob will = "for".

Any help is incredibly appreciated! Thanks in advance!

[StephenR]

It would help if you posted a small sample workbook.

[cbsalt]

Here's a sample of what I'm working with. I'm sorry I can't post more detailed information from the actual workbook, but it contains sensitive information. Please let me know if I should provide any additional information!

[StephenR]

OK, I'm a bit hazy on the column H business, but see if this sets you off in the right direction. Right-click Instructions tab, View Code and paste. It runs whenever B15 is changed:

Please Login or Register  to view this content.

[HelpCBW]

I have a similiar situation but the information in my macro appears a little different. This was an inherited macro. I would like to be able to change cell colors based on multiple contents. For instance, the current macro changes the row to color green based on content "10 SHOP". I would like to also add a different color for the same column based on a different content. How can I do this? I have added the current macro description below. So, basically if the content is "10 SHOP" it will change the color to green but if it is "LEASERTN" it will change it to another color. Thanks


LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row 'Number of rows on the sheet
Range(Range("AJ2"), Range("AJ" & LastRow)).Select
For Each cell In Selection
'If IsNull(cell) = False And cell <> "" Then
If IsNull(cell) = False And cell = "10 SHOP " Then
cell.EntireRow.Interior.ColorIndex = 4 'green highlight
'cell.EntireRow.Font.ColorIndex = 4 'green font
'cell.EntireRow.Font.Bold = True 'bold font
Else
'cell.EntireRow.Font.ColorIndex = xlAutomatic 'green font
'cell.EntireRow.Font.Bold = False 'bold font
End If
Next cell

Range("A2").Select

End Sub