[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Conditional Formatting

Posted on 2011-09-15
9
Medium Priority
?
416 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 2
  • 2
9 Comments
 
LVL 50
ID: 36547367
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 400 total points
ID: 36547425
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
ID: 36547446
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!

 
LVL 50

Accepted Solution

by:
Ingeborg Hawighorst (Microsoft MVP / EE MVE) earned 1600 total points
ID: 36547473
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
 
LVL 50
ID: 36547484
@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
ID: 36547488
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
ID: 36547558
teylyn,

macro tested in excel 2003.
0
 
LVL 50
ID: 36547643
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
ID: 36553031
Hi teylyn,

Thanks a lot, this is just sufficient
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
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.

656 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