Link to home
Create AccountLog in
Avatar of Cartillo
CartilloFlag for Malaysia

asked on

Highlight Cell

Hi Experts,

All data at Column D from “Data” sheet are copied to Week1 to Week5 sheets according to date and time (Column A- Data sheet). I would like to request Experts help create a macro which is able to highlight a cell in Week1 to Week5 in “Yellow” if that data has “TypeA” at Column B (Data Sheet). And, highlight with “Gray” if the Data matched with “TypeB” (Column  B – Data Sheet). E.g. Cell B6 (week1) is highlighted with “yellow” since this data has identified with “TypeA” at Data Sheet (B9). Similar goes with Cell C10 (week1) is highlighted in “Gray” since the data is identified with “TypeB” (C38-Data sheet). Attached the workbook for Experts perusal.

Avatar of Jacques Geday
Jacques Geday
Flag of Canada image

Link to home
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of Cartillo


Hi Gowflow.

Thanks for code, indeed this is what I’m looking for. I noticed few data have been missing at Week sheets, especially at every end and start of the day. Example, Title 85-87, 110-112,130-134,154-156, 178-180 are not available in the week sheets. I believe this data were not extracted from the day one. Sorry for overlooking this issue in the previous question.  Please help.
I downloaded the file you posted !!!! and it is missing this data in data sheet !!!
check you data sheet
I think I know why you created Type1 and draged it down all the way so it created Type2 Type3 etc in between dates there is 4 blank rows the last of the day was
as there are 4 blank rows deleting 85,86,87 gave you this data missing !!!

Yes, I've checked the whole data. The data available in "Data Sheet" but not in week sheets.
No the file posted here in this question is missing these !!
Hi Goflow,

The black cells are required to make a clear difference between date. Without this blank row it’s very difficult for user to identify the deference between date. Is that a way for us to overcome this?
you said the data was there ... I said the data in the file is not there
Which one of us is correct  ??? Did you check the file posted in this question ?? Am i right or I am dreaming ????

Don't get me wrong you/I need the blank row they are essential for trapping days I was just explaining what might have happened and why it is missing these ! that's all

Hi Goflow,

Looks like I'm the one who's sleeping!! sorry for the trouble. I'm comparing with my old data. Sorry for that. Based on posted workbook, these are the missing data:

Title 130
Title 490
Title 491
Title 512
Title 673
Title 696
Hi Hi Goflow,

This could annoying you, I'm so sorry.
no sorry I had apptmt to the doc looking at it now
Here it is:
22:55      TypeA      Title130      2:00
20:40      TypeA      Title490      2:15
22:55      TypeA      Title491      2:20
21:10      TypeA      Title512      2:05
22:55      TypeA      Title673      2:05
20:40      TypeA      Title696      2:05

Well how to you expect us to find these titles for the times mentioned if they do not exist in the week ???? 20:40 22:55 21:10 22:55 are all times that do not exist !!!!

I rest my case !!!
Hi gowflow,

OMG! you're right. I'm getting more absorbed with "titles" and totally ignored with the time set at Week sheets which is 15mins gap in between :(
Hi gowflow,

My sincere apology. I need to fix my timing first in order for me to get this thing correct.
no problem take your time... and we can cater the macro the way you like !!! let me know
Any news? Hope it was not too bad of a news this time interval issue
Hi gowflow,

Modification of the time interval value from 15min to 5min, the whole data are copied perfectly and highlight with the correct color code as expected. For sure, I can't obtain this result without your sharp eyes! Thanks a lot for helping to identify the actual root cause. Attached the workbook for kind reference.
ok glad you were able to fix it.
Thanks for the help
Hi gowflow,

Hope you will consider this request. Using your solution but need to extent the function.