• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 418
  • Last Modified:

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
0
Cartillo
Asked:
Cartillo
  • 5
  • 2
  • 2
2 Solutions
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
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
 
Shanmuga SundaramCommented:
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
 
CartilloAuthor Commented:
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
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
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
@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
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
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
 
Shanmuga SundaramCommented:
teylyn,

macro tested in excel 2003.
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
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
 
CartilloAuthor Commented:
Hi teylyn,

Thanks a lot, this is just sufficient
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 5
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now