Change cell color if sheet name in cell matches exactly

WORKBOOK called master log list

first sheet called master log
2nd sheet called 123456
3rd sheet called 456789
4th sheet called 789123
5th sheet called 456123
etc etc etc

cell H2 on master log sheet , I would like to turn this cell green and show word complete if cell E2 on same sheet matches any of the subsequent sheet names.


so basically here's my arguments

with formula in cell H2 on sheet called MASTER LOG

change this cell (H2) to green and display the word COMPLETE if cell E2 on this sheet matches exactly the name of any other sheet in this workbook. otherwise leave it blank.



thanks for any help here.

mike rand

Right Click on "Master Log" at the bottom of excel and select view code.

Paste this code in the module that opens and close it

Select E2, Type 123456 and then Enter.

Please Login or Register  to view this content.

Ive followed your instructions and when i type in 123456 into cell e2 it comes up with a

compile error
Next without for


not sure what that means.

thanks

mike rand

This formula will pulls sheet name for you - you then just need to compare that with whatever you want
=MID(CELL("Filename",A1),FIND("]",CELL("Filename",A1))+1,255)

not sure how to post an attachment of the workbook so you may be able to see whats wrong with code easier.

can you help?

thanks
mike rand

ps ive figured it out i need to start a new post to do it i cant add attachment to replys

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

wowsers,


fluff
whatever you changed it worked perfectly it now searches and finds the sheet with the same work order and makes h2 green and complete.

however
it only works in row 2
also when i remove the workorder number from e2 it doesnt remove the green and work complete from h2.
when i put another workorder in e3 it does not search or change to green and show complete.

it may be that i wasn't clear that i needed it to continue down the whole column. my bad

thanks for your awesome work and help so far.

mike rand

perfect

awesome.

did you see the post on the last problem i am having about it only working one way.


your code works perfect now but i noticed that if i put all my workorders in the column e then create the material takeoffs it doesnt look for them and turn them green and complete

it only works if i create the material takeoff first then put the workorder number into the column e

is there a way that it will work both ways.

if not both ways it would better suit the application for me to be able to list all my work orders and as i create the material takeoffs have them change to green complete while still having them go blank as i need to delete them from the list.

thanks

mike rand

My Apologies

Please Login or Register  to view this content.