Highlight Data

Hi Experts,

I would like to request Experts help. Need to highlight the data in Red color automatically at Detail sheet if the data has identified with “Yes” in Column I at “Daily Tracking List”. I have manually highlighted few data at Detail Sheet for Experts get better view. How expert will help me to add this function.



Chart-V7.xls
CartilloAsked:
Who is Participating?
 
Rory ArchibaldConnect With a Mentor Commented:
How's this?
Chart-V7.xls
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello,

what version of Excel would you like this to run in? If you use Excel 2010, you could probably do it with Conditional formatting, but in earlier versions, or for compatibility mode you would need a macro.

cheers, teylyn
0
 
CartilloAuthor Commented:
Hi teylyn,


I’m using either xls 2003 or 2007 when working with this workbook.
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
OK,

Since pre 2010 you cannot reference other sheets in Conditional Formatting formulas, I suggest using helper cells. I've set up formulas starting in cell AD2 across to AP2, copied down. The formula is

=INDEX('Daily Tracking List'!$I$1:$I$1000,MATCH($B2&D$1,INDEX('Daily Tracking List'!$B$1:$B$1000&'Daily Tracking List'!$D$1:$D$1000,0),0))="yes"

and it will result in either True, False or N/A.

Now we can use a simple conditional format in columns D to P. Select D2 to P54 and create a conditional format with the formula

=AD2

then select a red font.

See attached.

cheers, teylyn


Copy-of-Chart-V7.xls
0
 
CartilloAuthor Commented:
Hi,

Thanks for formula. Noticed few cells were not highlighteded even though it has marked with “Yes”. E.g. Cell at E6,F10 and G6. Please assist
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Connect With a Mentor Microsoft MVP ExcelCommented:
Ah, now I see that you are querying a time frame. The formula in the helper cells just got an awful lot uglier and much longer, but I think the result is now correct.

cheers, teylyn


Copy-of-Chart-V7.xls
0
 
CartilloAuthor Commented:
Hi teylyn

I have transferred the formula into my actual workbook and its highlighted at different area. I have attached the actual data that was used. Not sure what when wrong. Please help.

Chart-V7.xls
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello Cartillo,

the formula in AD2 should be in AD3. It references B3, where it should reference B2.

Select AD2, copy the formula from the Formula bar. Do not copy the cell!!  Hit F2 and copy the formula from the formula bar.
Select AD3 and delete the current contents, hit F2 and paste the copied formula into the formula bar.
Copy cell AD3 to cell AD2. Now the formula in AD2 should show the reference to B2.
Copy AD2 across to AP2.
Copy AD2:AP2 down to row 54.

cheers, teylyn
0
 
CartilloAuthor Commented:

Hi teylyn,

Sorry for the trouble, I’ve copied the formula but its not highlighting one of the row (P6), by right it should. Attached the workbook for your kind perusal. Again, sorry for inconvenience.  

Chart-V7.xls
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hi Cartillo,

I'm afraid this is getting too complex for the helper cell scenario. There are several entries for the same date and the same TH13, and the Index/Match will only find the first instance, which is a NO in column I.

I will call on some experts who may be able to try a macro approach.

cheers, teylyn
0
 
SiddharthRoutCommented:
Nice one Rory!

Sid
0
 
Rory ArchibaldCommented:
Don't know if it's right yet...
0
 
SiddharthRoutCommented:
As per my understanding It is. I tested it :)

Sid
0
 
Rory ArchibaldCommented:
Me too, but questions aren't always what they appear to be. ;)
0
 
SiddharthRoutCommented:
Hmmm. You are right.

Sid
0
 
CartilloAuthor Commented:
Hi Rory,

Need your advice, If Type data (Column D) only consists of Alphabet (without number, e.g. TBD, BNFDF, TGFD) does this impact the resultS. I noticed when I use the formula it shows "REF!" .
0
 
Rory ArchibaldCommented:
My formula shouldn't care about that, but that doesn't match your DV settings so one of your other formulas might be returning an error value.
0
 
CartilloAuthor Commented:
Hi Rory,

DV setting?  How to check this?
0
 
Rory ArchibaldCommented:
In your column you have Data Validation set up to restrict what you can enter. Currently it only allows TH1 to TH13 so I don't know where your new codes are coming from.
To view the DV settings, select a cell in the Type column and choose Data-Validation from the menu.
0
 
CartilloAuthor Commented:
Hi Rory,

I have check the setting, all looks perfect. The funny part is, I can test and view all data are displayed perfectly in your attached file. But, when used the same formula in my actual wb it show and error message in cells as "#Name?".

Is that other way for us to get the same result?
0
 
Rory ArchibaldCommented:
That's because you haven't set up the 3 named ranges I used. I assumed this was your file, so will need to add the 3 ranges mentioned in the formulas to your workbook (you can review them under insert-name-define in XL2003.)
0
 
CartilloConnect With a Mentor Author Commented:
Thanks a lot Rory. Works perfectly after defining the range.
0
 
CartilloAuthor Commented:
Hi Rory/teylyn,

Thanks a lot for helping me create this function.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.