We help IT Professionals succeed at work.

Highlight Cell

Cartillo
Cartillo asked
on
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.

Copy-DataNew.xls
Comment
Watch Question

Partner
CERTIFIED EXPERT
Commented:
Is this what your looking for ?
gowflow
Copy-DataNew.xls

Author

Commented:
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.
gowflowPartner
CERTIFIED EXPERT

Commented:
I downloaded the file you posted !!!! and it is missing this data in data sheet !!!
check you data sheet
gowflow
gowflowPartner
CERTIFIED EXPERT

Commented:
I think I know why you created Type1 and draged it down all the way so it created Type2 Type3 etc ...so in between dates there is 4 blank rows the last of the day was
Type84
Type85
Type86
Type87
Type88
as there are 4 blank rows deleting 85,86,87 gave you this data missing !!!
gowflow

Author

Commented:
Hi,

Yes, I've checked the whole data. The data available in "Data Sheet" but not in week sheets.
gowflowPartner
CERTIFIED EXPERT

Commented:
No the file posted here in this question is missing these !!
gowflow

Author

Commented:
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?
gowflowPartner
CERTIFIED EXPERT

Commented:
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

gowflow

Author

Commented:
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

Author

Commented:
Hi Hi Goflow,

This could annoying you, I'm so sorry.
gowflowPartner
CERTIFIED EXPERT

Commented:
no sorry I had apptmt to the doc looking at it now
gowflow
gowflowPartner
CERTIFIED EXPERT

Commented:
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 !!!
gowflow

Author

Commented:
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 :(

Author

Commented:
Hi gowflow,

My sincere apology. I need to fix my timing first in order for me to get this thing correct.
gowflowPartner
CERTIFIED EXPERT

Commented:
no problem take your time... and we can cater the macro the way you like !!! let me know
gowflow
gowflowPartner
CERTIFIED EXPERT

Commented:
Any news? Hope it was not too bad of a news this time interval issue
gowflow

Author

Commented:
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.
Merge-Cell.xls
gowflowPartner
CERTIFIED EXPERT

Commented:
ok glad you were able to fix it.
gowflow

Author

Commented:
Hi,
Thanks for the help

Author

Commented:
Hi gowflow,

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


http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_27438817.html

Explore More ContentExplore courses, solutions, and other research materials related to this topic.