Solved

Conditional Formatting

Posted on 2011-09-15
9
403 Views
Last Modified: 2012-05-12
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
0
Comment
Question by:Cartillo
  • 5
  • 2
  • 2
9 Comments
 
LVL 50

Expert Comment

by:teylyn
Comment Utility
Hello Cartillo,

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
0
 
LVL 17

Assisted Solution

by:Shanmuga Sundaram
Shanmuga Sundaram earned 100 total points
Comment Utility
I used this macro and found this to work

Sub format()
Dim sourcecell1 As String
Dim sourcecell2 As String
Dim found As Boolean
Dim data As String
For I = 6 To 342
Sheets(1).Select
sourcecell1 = Cells(I, 1)
sourcecell2 = Cells(I, 2)
Sheets("data").Select
found = False
For j = 2 To 360
If UCase(sourcecell2) = "" Or InStr(1, UCase(sourcecell2), "--", vbTextCompare) > 0 Or InStr(1, UCase(sourcecell1), ":", vbTextCompare) = 0 Then GoTo Resme
If UCase(sourcecell1) = UCase(Cells(j, 1).Text) And UCase(sourcecell2) = UCase(Cells(j, 3).Text) Then
found = True
Else
If UCase(sourcecell2) = UCase(Cells(j, 3).Text) Then
found = True
Sheets(1).Select
Cells(I, 2).Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = vbYellow
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
GoTo Resme
End If
End If
Next
If found = False Then
Sheets(1).Select
Cells(I, 2).Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = vbRed
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
End If
Resme:
Next
End Sub
0
 

Author Comment

by:Cartillo
Comment Utility
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.
0
 
LVL 50

Accepted Solution

by:
teylyn earned 400 total points
Comment Utility
Slightly amended version to make sure only the rows with time data in column A are evaluated.

These are the three named ranges I created:


Cert      =Data!$C$2:$C$170
Time      =Data!$A$2:$A$170
nTime      =TEXT(Time,"hh:mm:ss.00")

These are the two conditional formatting formulas:

=AND(ISNUMBER(A6+0),B6<>"",ISNA(MATCH(A6&B6,INDEX(nTime&Cert,0),0)))   -- red
=AND(ISNUMBER(A6+0),B6<>"",MATCH(B6,Cert,0))   -- yellow

Applied in this order with Stop if True.

cheers, teylyn
Highlight-Data3--1-.xls
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 50

Expert Comment

by:teylyn
Comment Utility
@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,
0
 
LVL 50

Expert Comment

by:teylyn
Comment Utility
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
0
 
LVL 17

Expert Comment

by:Shanmuga Sundaram
Comment Utility
teylyn,

macro tested in excel 2003.
0
 
LVL 50

Expert Comment

by:teylyn
Comment Utility
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
0
 

Author Closing Comment

by:Cartillo
Comment Utility
Hi teylyn,

Thanks a lot, this is just sufficient
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now