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
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!
Bookmarks