Conditional Formatting
Hi Experts,
I would like to request Experts help to create a Conditional Formatting to crosscheck data at Source sheet (column A and B) with Column A and C at Data sheet. Here's condition:
> If Data at Source sheet (Column A and B) not matched with data at Column A and C, highlight the cell at column B (Source sheet) with red color.
> If only Data B (Source Sheet) matched with data at Column C (Data sheet), highlight the cell at column B (Source sheet) with Yellow color. The conditional only active if both Column A and B (both sheet) with data (need ti ignore "--------" and other words which is not relevant.
Hope Experts will help me create this Conditional Formula. Attached the workbook for Experts perusal.
Highlight-Data3.xls
I would like to request Experts help to create a Conditional Formatting to crosscheck data at Source sheet (column A and B) with Column A and C at Data sheet. Here's condition:
> If Data at Source sheet (Column A and B) not matched with data at Column A and C, highlight the cell at column B (Source sheet) with red color.
> If only Data B (Source Sheet) matched with data at Column C (Data sheet), highlight the cell at column B (Source sheet) with Yellow color. The conditional only active if both Column A and B (both sheet) with data (need ti ignore "--------" and other words which is not relevant.
Hope Experts will help me create this Conditional Formula. Attached the workbook for Experts perusal.
Highlight-Data3.xls
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi teylyn,
Thanks for the formula. Sorry for using the compatible version, my Desktop only has 2003, but my laptop with 2007. Pretty soon I'm going to use 2007 in my Desktop.
Is that way to convert the Source sheet data into time format similar as Data Sheet and convert all time starts with 24 to 30 into 00 to 06 hrs (e.e. 24:10:00.0 to 00:10:00.0 and 29:10:00.0 to 05:10:00.0). This could solve the whole issue. Hope you will consider this.
Thanks for the formula. Sorry for using the compatible version, my Desktop only has 2003, but my laptop with 2007. Pretty soon I'm going to use 2007 in my Desktop.
Is that way to convert the Source sheet data into time format similar as Data Sheet and convert all time starts with 24 to 30 into 00 to 06 hrs (e.e. 24:10:00.0 to 00:10:00.0 and 29:10:00.0 to 05:10:00.0). This could solve the whole issue. Hope you will consider this.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
@shasunder,
did you test that code in Excel 2003? Seeing that the file extension is xls, I'd assume it needs to work in that version, but it comes up with errors. Also, as I read it, Cartillo is asking specifically for conditional formatting, not a macro.
cheers,
did you test that code in Excel 2003? Seeing that the file extension is xls, I'd assume it needs to work in that version, but it comes up with errors. Also, as I read it, Cartillo is asking specifically for conditional formatting, not a macro.
cheers,
Hello Cartillo,
I did not see your comment before I posted the amended version. The file I posted works fine with the formats as they are, since the range names I use will do the conversion.
Do you want to convert the text values to time values or the other way round? In any case, this would be a different question, better suited for a new thread to attract more experts.
cheers, teylyn
I did not see your comment before I posted the amended version. The file I posted works fine with the formats as they are, since the range names I use will do the conversion.
Do you want to convert the text values to time values or the other way round? In any case, this would be a different question, better suited for a new thread to attract more experts.
cheers, teylyn
teylyn,
macro tested in excel 2003.
macro tested in excel 2003.
Interesting. When I run it in XL 2003, it throws an error at
.TintAndShade = 0
Runtime error 438
Object does not support this property or method
Because that property does not exist in XL 2003
.TintAndShade = 0
Runtime error 438
Object does not support this property or method
Because that property does not exist in XL 2003
ASKER
Hi teylyn,
Thanks a lot, this is just sufficient
Thanks a lot, this is just sufficient
The time columns are stored as text in one sheet and as time in another. That presents a few problems.
I created a few range names, because conditional formats before Excel 2010 cannot refer to ranges on other sheets.
See if the attached does what you need.
Also, you always post .xls files, but they have clearly been created in a later version. If you use 2007 or later, why not post the original file format? That would make things a lot easier.
cheers, teylyn
Highlight-Data3.xls