cpatte7372
asked on
Conditional Formatting with Excel - Part 3
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
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
Do you mean exactly equal to L2-0.02 or greater than or equal to that?
Use this for conditional formatting for cell D2
=ROUND($L$2,2)-0.02
=ROUND($L$2,2)-0.02
ASKER
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....
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....
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
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
ASKER
hitsdoshi1
I got a compile error with:
For ctr
I got a compile error with:
For ctr
ASKER
Its says 'variable not defined'
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
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
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
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
ASKER
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
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
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
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
ASKER
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
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
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...
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
ASKER
hitsdoshi1
I think I've managed to get it working without any errors, however there isn't any sound when I change the number.....
I think I've managed to get it working without any errors, however there isn't any sound when I change the number.....
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...
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...
ASKER
hitsdoshi1
Thats great, still having a problem with the sound....
Thats great, still having a problem with the sound....
why don't you forward me your code again and let me check what changes you have made...
ASKER
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
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
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.
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.
ASKER
hitsdoshi1,
You wrote D2 "0.98" and L2 "10-0.02". Did you mean D2 9.98 and L2 10.00?
You wrote D2 "0.98" and L2 "10-0.02". Did you mean D2 9.98 and L2 10.00?
ASKER
hitsdoshi1,
What might be a good idea is if you update the attachment with the code yourself and post it back.
What might be a good idea is if you update the attachment with the code yourself and post it back.
ASKER
hitsdoshi1
Its amazing how its working for you. I don't even get the MsgBox "This Works"
Its amazing how its working for you. I don't even get the MsgBox "This Works"
ASKER
Ahhhhhh,
It seems to work when you click on 'Run/UserForm and the cursor is under Sub CheckD()
But it won't run automatically.....
It seems to work when you click on 'Run/UserForm and the cursor is under Sub CheckD()
But it won't run automatically.....
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ok, follow the instruction in my last comment and it will run automatically
ASKER
hitsdoshi1,
Nearly there, I'm getting a Type Mismatch with:
If Range("D" & ctr) = "" Then
ASKER
hitsdoshi1,
My mistake, its working fine.....
Thanks man.....
Really, really appreciate it...
My mistake, its working fine.....
Thanks man.....
Really, really appreciate it...
Just add this line
If (IsError(Range("D" & ctr))) Then Exit Sub
before
if Range("D" & ctr)="" Then..
If (IsError(Range("D" & ctr))) Then Exit Sub
before
if Range("D" & ctr)="" Then..
ASKER
hitsdoshi
As everything is working fine, should I still add
If (IsError(Range("D" & ctr))) Then Exit Sub
As everything is working fine, should I still add
If (IsError(Range("D" & ctr))) Then Exit Sub
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
ASKER
ok, thanks again mate..
ASKER
Excellent!