Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

More Excel Conditional Formatting Issues

Posted on 2011-02-18
16
Medium Priority
?
298 Views
Last Modified: 2012-05-11
Hi Experts,

I've added a conditional format to a range of cells. It appear simple but its not working.

Basically, I've created a condition that states if values in column Q are equal to the values in column D then highlight cell blue.

As you'll see from the sample, in the first row I have D2 at 16.65 which is the same value as that of Q2, but the cell isn't highlighted.

Can someone explain why?

Cheers

Carlton
Trade-Day-sample.xlsm
0
Comment
Question by:cpatte7372
[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
  • 7
  • 3
  • 3
  • +2
16 Comments
 
LVL 33

Accepted Solution

by:
jppinto earned 1000 total points
ID: 34925486
That because the REAL value of the cell Q2 is not 16,65, it's 16,647! There is a diference between the real value and the displayed value. If you increase the number of decimals, in column Q you will see the real values.

jppinto
0
 
LVL 33

Expert Comment

by:jppinto
ID: 34925497
I've putted on cell Q2, the value of 16,65. As you can see on the attached image, the conditional formatting is working.

jppinto
Capture.JPG
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 34925507
If you format D2 to show 10 decimal places it looks like this

16.6500000000

but Q2 blooks like this

16.6465502959

so the two aren't the same (conditional formatting looks at the underlying value not the displayed value).

You could try using a formula with ROUND, e.g. select Q2:Q112 and apply this formula in conditional formatting

=ROUND(Q2,2)=D2

regards, barry
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 50

Expert Comment

by:barry houdini
ID: 34925528
...or another option would be to change the worksheet formula in Q2 and down, e.g. change to this

=ROUND(Y2/M2,2)

regards, barry
0
 

Author Comment

by:cpatte7372
ID: 34925785
Barry,

I'm going with your second option.

Going to test it out later when the markets open. If all goes well I'll close it.

Cheers

0
 
LVL 50

Assisted Solution

by:barry houdini
barry houdini earned 1000 total points
ID: 34925970
OK, If you are keeping the existing formatting I note that you have

"Cell value is equal to " =$D$2

with the $ signs that means that this won't adjust by row so you are comparing all of the Q2:Q112 values against D2, if you want to compare Q3 against D3, Q4 against D4 etc. then remove the $ signs, i.e. use

"Cell value is equal to " =D2

regards, barry


0
 

Author Comment

by:cpatte7372
ID: 34927106
ohhhhhhhh, I see.

And one other thing.

If a value in column D was say 17.5123 and the value in column Q was 17.51 due rounding it up with =ROUND(Y2/M2,2)  would I get a match?

Cheers
0
 

Author Comment

by:cpatte7372
ID: 34927250
So Barry,

Looking at the attached sample will I get a match?

Cheers
Trade-Day-sample.xlsm
0
 

Author Comment

by:cpatte7372
ID: 34927454
Barry,

I very clever expert called Brian wrote the following to alert me when a condition occurred.

Would you know how to apply it to this situation?

Option Explicit

Private Sub Worksheet_Calculate()
Dim xCell As Range
Dim xLastRow As Long
Dim xValue As Variant

xLastRow = ActiveSheet.Range("A1").SpecialCells(xlLastCell).Row

If xLastRow < 2 Then Exit Sub

For Each xCell In Range("O2:O" & xLastRow)
    xValue = xCell.Value
    If Not IsError(xValue) Then
        If xValue <> "" And xValue < 0.0002 Then
            Beep
            Cells(1, 15).Interior.Color = 255
            Exit Sub
        End If
    End If
Next

Cells(1, 15).Interior.Color = 65535

End Sub



Brian/Fernando I'm going close the other request as I'm happy with just the conditional formatting. If you see this can you help me out?

Cheers

Carlton
0
 

Author Comment

by:cpatte7372
ID: 34927461
Basically, I would like the formula the Brian wrote applied to the conditional formatting in the sample.

Cheers gentlemen
0
 

Author Comment

by:cpatte7372
ID: 34927554
Experts,

Don't want to appear rude or impatient, but if you do get a moment to help please do, as the best time to test any resolutions is while the stock markets are open.

Cheers
0
 
LVL 26

Expert Comment

by:redmondb
ID: 34928044
cpatte7372,

I appreciate the kind words, but you're asking a new question. Could I suggest you close this one, allocate the points to the expert(s) involved and open a new one? (This really isn't a fishing expedition for potentially more points for me as you know my unresolved concerns about your external application!)

Cheers,
Brian.
0
 

Author Closing Comment

by:cpatte7372
ID: 34928209
Thanks
0
 
LVL 33

Expert Comment

by:jppinto
ID: 34928310
cpatte7372, please allow me to disagree with the points assignment!

I was the first to post an explanation of what was the problem that you had with your conditional formatting and you didn't even gave me an "assist" on my answers?!

Please explain...

jppinto
0
 
LVL 1

Expert Comment

by:modus_operandi
ID: 34986507
cpatte7372,
 
I have reopened your question and started the process of closing it with a split amongst two or more Experts, as that outcome seems more equitable than your original disposition.
 
modus_operandi
EE Admin
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

730 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