Solved

Conditional Formatting with Excel - Part 3

Posted on 2011-03-02
31
225 Views
Last Modified: 2012-06-27
Hello experts,

I have conditional format in column L that highlights the cell when the value = value in cell D2. I would like to tweak this conditon to highlight when the cell in D2 is L2 - 0.02. So, in the example D2 is currently 37.24 and L2 is 37.28. I would like the cell to highlight when the value in D2 reaches 37.26.

Thanks in advance.
Trade-Day--10-Experts-1-.xlsm
0
Comment
Question by:cpatte7372
  • 17
  • 13
31 Comments
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35018258
Do you mean exactly equal to L2-0.02 or greater than or equal to that?
0
 
LVL 9

Expert Comment

by:hitsdoshi1
ID: 35018417
Use this for conditional formatting for cell D2

=ROUND($L$2,2)-0.02
0
 

Author Comment

by:cpatte7372
ID: 35018809
hitsdoshi1

That worked.

Would you know how to attach somekind of sound alert to that. At the moment, I have the following alert on column L:

For Each xCell In Range("l2:l" & xLastRow)
    xValue = xCell.Value
    xValue2 = xCell.Offset(0, -8).Value
    If Not IsError(xValue) And Not IsError(xValue2) Then
        If xValue <> "" And xValue2 <> "" And Round(xValue, 2) = Round(xValue2, 2) Then
            PlayTheSound "Windows XP Print complete.wav"

            Cells(1, 12).Interior.Color = 255
            Exit Sub
        End If
    End If
Next

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


Do you think I could use that for column D?

Cheers mate....
0
 
LVL 9

Expert Comment

by:hitsdoshi1
ID: 35019087
I will go with something simple like this...

For ctr = 2 To 65000
DVal = Round(Range("D" & ctr).Value, 2)
Lval = (Round(Range("L" & ctr).Value, 2)) - 0.02

If Range("D" & ctr) = "" Then Exit For

If DVal = Lval Then
      PlayTheSound "Windows XP Print complete.wav"
End If

Next
0
 

Author Comment

by:cpatte7372
ID: 35019366
hitsdoshi1

I got a compile error with:

For ctr

0
 

Author Comment

by:cpatte7372
ID: 35019372
Its says 'variable not defined'
0
 
LVL 9

Expert Comment

by:hitsdoshi1
ID: 35019497
Try this one...

Sub CheckD()
Dim ctr As Long
For ctr = 2 To 65000
    If Range("D" & ctr) = "" Then Exit For
    If Range("D" & ctr) = Round(Range("L" & ctr).Value, 2) - 0.02 Then
          PlayTheSound "Windows XP Print complete.wav"
    End If
Next
End Sub
0
 
LVL 9

Expert Comment

by:hitsdoshi1
ID: 35019527
You can write exit sub instead of exit for....

Sub CheckD()
Dim ctr As Long
For ctr = 2 To 65000
    If Range("D" & ctr) = "" Then Exit Sub
    If Range("D" & ctr) = Round(Range("L" & ctr).Value, 2) - 0.02 Then
          PlayTheSound "Windows XP Print complete.wav"
    End If
Next
End Sub
0
 

Author Comment

by:cpatte7372
ID: 35020086
hitsdoshi1

Thanks again for responding.

I keep on getting the error message 'Expected End Sub'. When I put in an end sub it keeps on giving me the same error message

0
 
LVL 9

Expert Comment

by:hitsdoshi1
ID: 35020154
How about this?

Sub CheckD()
Dim ctr As Long
For ctr = 2 To 65000
    If Range("D" & ctr) = "" Then
          Exit Sub
    Endif
    If Range("D" & ctr) = Round(Range("L" & ctr).Value, 2) - 0.02 Then
          PlayTheSound "Windows XP Print complete.wav"
    End If
Next
End Sub
0
 

Author Comment

by:cpatte7372
ID: 35020202
hitsdoshi1

As you can see from the attached sample, I've placed the code you suggested in but I keep on getting the error when I changed the value from 10.00 in cell D2 to 9.98
Trade-Day--10-Experts-2-.xlsm
0
 
LVL 9

Expert Comment

by:hitsdoshi1
ID: 35020275
Its cos you are inserting the sub CheckD() in another Sub Worksheet_Calculate(), and that's what causes the error...

This is what you have...

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


xLastRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).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
            Cells(1, 15).Interior.Color = 255
            Point02 = True
            Exit For
        End If
    End If
Next



If Not Point02 Then Cells(1, 15).Interior.Color = 65535

For Each xCell In Range("q2:q" & xLastRow)
    xValue = xCell.Value
    xValue2 = xCell.Offset(0, -13).Value
    If Not IsError(xValue) And Not IsError(xValue2) Then
        If xValue <> "" And xValue2 <> "" And Round(xValue, 2) = Round(xValue2, 2) Then
            PlayTheSound "tada.wav"

            Cells(1, 17).Interior.Color = 255
            Exit Sub
        End If
    End If
Next

Cells(1, 17).Interior.Color = 65535
If Not Point02 Then Cells(1, 12).Interior.Color = 65535

Sub CheckD()
Dim ctr As Long
For ctr = 2 To 65000
    If Range("D" & ctr) = "" Then Exit Sub
    If Range("D" & ctr) = Round(Range("L" & ctr).Value, 2) - 0.02 Then
          PlayTheSound "Windows XP Print complete.wav"
    End If
Next
End Sub

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


End Sub



This is what you need.....just remove the lines 9 to 65 and paste the following code...

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


xLastRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).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
            Cells(1, 15).Interior.Color = 255
            Point02 = True
            Exit For
        End If
    End If
Next



If Not Point02 Then Cells(1, 15).Interior.Color = 65535

For Each xCell In Range("q2:q" & xLastRow)
    xValue = xCell.Value
    xValue2 = xCell.Offset(0, -13).Value
    If Not IsError(xValue) And Not IsError(xValue2) Then
        If xValue <> "" And xValue2 <> "" And Round(xValue, 2) = Round(xValue2, 2) Then
            PlayTheSound "tada.wav"

            Cells(1, 17).Interior.Color = 255
            Exit Sub
        End If
    End If
Next

Cells(1, 17).Interior.Color = 65535
If Not Point02 Then Cells(1, 12).Interior.Color = 65535
Cells(1, 12).Interior.Color = 65535


End Sub

Sub CheckD()
Dim ctr As Long
For ctr = 2 To 65000
    If Range("D" & ctr) = "" Then Exit Sub
    If Range("D" & ctr) = Round(Range("L" & ctr).Value, 2) - 0.02 Then
          PlayTheSound "Windows XP Print complete.wav"
    End If
Next
End Sub

Open in new window

0
 

Author Comment

by:cpatte7372
ID: 35020324
hitsdoshi1

I think I've managed to get it working without any errors, however there isn't any sound when I change the number.....
0
 
LVL 9

Expert Comment

by:hitsdoshi1
ID: 35020415
it only plays sound if D# is equal to L#-0.02

I just checked with your file, where D2=10 and L2=10, so I changed the value of D2 to 9.98....so in the code now it matches D2=L2-0.02......and it plays the sound...
0
 

Author Comment

by:cpatte7372
ID: 35020491
hitsdoshi1

Thats great, still having a problem with the sound....
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 9

Expert Comment

by:hitsdoshi1
ID: 35020508
why don't you forward me your code again and let me check what changes you have made...
0
 

Author Comment

by:cpatte7372
ID: 35020594
hitsdoshi

Thanks, here you go....


Option Explicit
Private Declare Function sndPlaySound32 _
    Lib "winmm.dll" _
    Alias "sndPlaySoundA" ( _
        ByVal lpszSoundName As String, _
        ByVal uFlags As Long) As Long


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


xLastRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).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
            Cells(1, 15).Interior.Color = 255
            Point02 = True
            Exit For
        End If
    End If
Next



If Not Point02 Then Cells(1, 15).Interior.Color = 65535

For Each xCell In Range("q2:q" & xLastRow)
    xValue = xCell.Value
    xValue2 = xCell.Offset(0, -13).Value
    If Not IsError(xValue) And Not IsError(xValue2) Then
        If xValue <> "" And xValue2 <> "" And Round(xValue, 2) = Round(xValue2, 2) Then
            PlayTheSound "tada.wav"

            Cells(1, 17).Interior.Color = 255
            Exit Sub
        End If
    End If
Next

Cells(1, 17).Interior.Color = 65535
If Not Point02 Then Cells(1, 12).Interior.Color = 65535
Cells(1, 12).Interior.Color = 65535


End Sub

Sub CheckD()
Dim ctr As Long
For ctr = 2 To 65000
    If Range("D" & ctr) = "" Then Exit Sub
    If Range("D" & ctr) = Round(Range("L" & ctr).Value, 2) - 0.02 Then
          PlayTheSound "Windows XP Print complete.wav"
    End If
Next
End Sub

Sub PlayTheSound(ByVal WhatSound As String)
    If Dir(WhatSound, vbNormal) = "" Then
        ' WhatSound is not a file. Get the file named by
        ' WhatSound from the Windows\Media directory.
        WhatSound = Environ("SystemRoot") & "\Media\" & WhatSound
        If InStr(1, WhatSound, ".") = 0 Then
            ' if WhatSound does not have a .wav extension,
            ' add one.
            WhatSound = WhatSound & ".wav"
        End If
        If Dir(WhatSound, vbNormal) = vbNullString Then
            ' Can't find the file. Do a simple Beep.
            Beep
            Exit Sub
        End If
    Else
        ' WhatSound is a file. Use it.
    End If
    ' Finally, play the sound.
    sndPlaySound32 WhatSound, 0&
End Sub







0
 
LVL 9

Expert Comment

by:hitsdoshi1
ID: 35020826
ok, just check the code line by line and its perfect. Works for me....the potential problem I can see is, if you are missing the sound file....just to make sure...do the following....

Insert this line....at the end right before End Sub and After sndPlaySound32

MsgBox "This works!!!"

and match one of the cell....for ex......make D2=L2-0.02.....so as per your earlier attachment, I had D2 as 0.98 and L2 was 10-0.02 and it matched and played the sound....so if it matches then it will play the sound and should pop up message window. If msg window pops up then code is fine, you might want to check the path of your sound file.
0
 

Author Comment

by:cpatte7372
ID: 35020976
hitsdoshi1,

You wrote D2 "0.98" and L2 "10-0.02". Did you mean D2 9.98 and L2 10.00?

0
 

Author Comment

by:cpatte7372
ID: 35020989
hitsdoshi1,

What might be a good idea is if you update the attachment with the code yourself and post it back.

0
 

Author Comment

by:cpatte7372
ID: 35021285
hitsdoshi1

Its amazing how its working for you. I don't even get the MsgBox "This Works"

0
 

Author Comment

by:cpatte7372
ID: 35021330
Ahhhhhh,

It seems to work when you click on 'Run/UserForm and the cursor is under Sub CheckD()

But it won't run automatically.....
0
 
LVL 9

Accepted Solution

by:
hitsdoshi1 earned 500 total points
ID: 35021341
On the other thought.......are you calling this function? You probably need the line

Call CheckD

after this code
If Not Point02 Then Cells(1, 12).Interior.Color = 65535
Cells(1, 12).Interior.Color = 65535

Anyway I attached the code, and I also added the line "call cehckD" as mentioned above.
EE.xls
0
 
LVL 9

Expert Comment

by:hitsdoshi1
ID: 35021349
ok, follow the instruction in my last comment and it will run automatically
0
 

Author Comment

by:cpatte7372
ID: 35021413

hitsdoshi1,

Nearly there, I'm getting a Type Mismatch with:

If Range("D" & ctr) = "" Then
0
 

Author Comment

by:cpatte7372
ID: 35021443
hitsdoshi1,

My mistake, its working fine.....

Thanks man.....

Really, really appreciate it...
0
 
LVL 9

Expert Comment

by:hitsdoshi1
ID: 35021471
Just add this line

  If (IsError(Range("D" & ctr))) Then Exit Sub

before

if Range("D" & ctr)="" Then..
0
 

Author Comment

by:cpatte7372
ID: 35021866
hitsdoshi

As everything is working fine, should I still add

  If (IsError(Range("D" & ctr))) Then Exit Sub

0
 
LVL 9

Expert Comment

by:hitsdoshi1
ID: 35021892
It just checks, like if there is division error or something, code will give you error message, so it doesn't harm in adding this line...but it will terminate the code if it encounters error
0
 

Author Comment

by:cpatte7372
ID: 35021910
ok, thanks again mate..
0
 

Author Closing Comment

by:cpatte7372
ID: 35021916
Excellent!
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

743 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