Hi,
I know how to use the conditionnal formatting, and I would like to know if it is possible to have an audio alert instead of just changing the format.
For instance, if the cell value fits the condition in the cell, I would like a sound to be played.
Can you point me in the right direction?
Sebastien
[vba]
Option Explicit
Const SND_SYNC = &H0
Const SND_ASYNC = &H1
Const SND_FILENAME = &H20000
Private Declare Function PlaySound Lib "winmm.dll" Alias "PlaySoundA" _
(ByVal lpszName As String, _
ByVal hModule As Long, _
ByVal dwFlags As Long) As Long
Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H1:H10" '<=== change to suit
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If .Value > 10 Then PlayWAVFile "chimes.wav"
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub
Sub PlayWAVFile(WavFile As String, Optional Async As Boolean = True)
WavFile = "C:\Windows\Media\" & WavFile
If Async Then
Call PlaySound(WavFile, 0&, SND_ASYNC Or SND_FILENAME)
Else
Call PlaySound(WavFile, 0&, SND_SYNC Or SND_FILENAME)
End If
End Sub
[/vba]
This is worksheet event code, which means that it needs to be
placed in the appropriate worksheet code module, not a standard
code module. To do this, right-click on the sheet tab, select
the View Code option from the menu, and paste the code in.
Ok thanks. I'll try this. I also need to add conditions since I only want to check when a value updated live from Internet crosses a barrier (I don't want the sound playing continuously but only once). I should be able to do it myself. I'll post back here if I have a problem.
Finally I have 2 questions. How can I fit the range. "H1:H10" is a string, how can I adapt to get an union of several nonconsecutive cells (ex1: A1, D1, F1, B2, E2).
2) How can I have a memory of the value? I would like to use a bool to say if I meet the condition or not and then activate the sound if the condition is true now and was false before or the opposite.
How can I have a bool whose value will remain in VBA for the worksheet, even when the code is not running?
1) You don't want a union, but an intersect. just change the range provided to those non-contiguous cells (comma separated)
2) For each cell, or overall?
Q1
[VBA]
Dim Rg As Range
Set Rg = Union(Range("A1"), Range("D1"), Range("F1"), Range("B2"), Range("E2"))
[/VBA]
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
Your answer defines a Range, but I thought I needed a string to fit this:
[VBA]
Const WS_RANGE As String = "H1:H10"
[/VBA]
Xld, I would need a different variable for each cell, but if you show me how to do it for one cell I will be able to extend.
Sorry, I hadn't read XLD's code properly. To use the range, this line would be amended
[VBA]
If Not Intersect(Target, Rg) Is Nothing Then
[/VBA]
but I would go with XLD's suggestion
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
Do you mean that I should write for instance:
[VBA]
Const WS_RANGE As String = "H1,H10,B5,B8"
[/VBA]
[VBA]Private Sub Worksheet_Change(ByVal Target As Range)Originally Posted by jungix
Application.EnableEvents = False
With Target
Select Case .Address(0, 0)
Case "A1"
If .Value > 10 Then PlayWAVFile "chimes.wav"
Case "D1"
If .Value > 20 Then PlayWAVFile "chimes.wav"
Case "F1"
If .Value > 30 Then PlayWAVFile "chimes.wav"
Case "B2"
If .Value > 40 Then PlayWAVFile "chimes.wav"
Case "E2"
If .Value > 50 Then PlayWAVFile "chimes.wav"
End Select
End With
ws_exit:
Application.EnableEvents = True
End Sub[/VBA]
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
I'm sure this can be tidied up, but try the following
[vba]
Option Explicit
Const SND_SYNC = &H0
Const SND_ASYNC = &H1
Const SND_FILENAME = &H20000
Private Declare Function PlaySound Lib "winmm.dll" Alias "PlaySoundA" _
(ByVal lpszName As String, _
ByVal hModule As Long, _
ByVal dwFlags As Long) As Long
Dim Val1 As Long
Dim Val2 As Long
Dim Val3 As Long
Dim Val4 As Long
Dim Val5 As Long
Private Sub Worksheet_Activate()
Val1 = Range("A1")
Val2 = Range("D1")
Val3 = Range("F1")
Val4 = Range("B1")
Val5 = Range("E1")
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
With Target
Select Case .Address(0, 0)
Case "A1"
If Val1 <= 10 And .Value > 10 Then PlayWAVFile "chimes.wav"
Case "D1"
If Val2 <= 20 And .Value > 20 Then PlayWAVFile "chimes.wav"
Case "F1"
If Val3 <= 30 And .Value > 30 Then PlayWAVFile "chimes.wav"
Case "B2"
If Val4 <= 40 And .Value > 40 Then PlayWAVFile "chimes.wav"
Case "E2"
If Val5 <= 50 And .Value > 50 Then PlayWAVFile "chimes.wav"
End Select
End With
Val1 = Range("A1")
Val2 = Range("D1")
Val3 = Range("F1")
Val4 = Range("B2")
Val5 = Range("E2")
ws_exit:
Application.EnableEvents = True
End Sub
Sub PlayWAVFile(WavFile As String, Optional Async As Boolean = True)
WavFile = "C:\Windows\Media\" & WavFile
If Async Then
Call PlaySound(WavFile, 0&, SND_ASYNC Or SND_FILENAME)
Else
Call PlaySound(WavFile, 0&, SND_SYNC Or SND_FILENAME)
End If
End Sub
[/vba]
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
That is tyhe way.
As an example
[vba]
Option Explicit
Const SND_SYNC = &H0
Const SND_ASYNC = &H1
Const SND_FILENAME = &H20000
Private Declare Function PlaySound Lib "winmm.dll" Alias "PlaySoundA" _
(ByVal lpszName As String, _
ByVal hModule As Long, _
ByVal dwFlags As Long) As Long
Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A1,B1,C1,D1"
Dim aryCellAddresses
Dim aryTestValues
Dim iPos As Long
On Error GoTo ws_exit:
Application.EnableEvents = False
aryTestValues = Array(10, 20, 30, 40)
aryCellAddresses = Split(WS_RANGE, ",")
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
iPos = Application.Match(.Address(False, False), aryCellAddresses, 0)
If .Value > aryTestValues(iPos - 1) Then PlayWAVFile "chimes.wav"
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub
Sub PlayWAVFile(WavFile As String, Optional Async As Boolean = True)
WavFile = "C:\Windows\Media\" & WavFile
If Async Then
Call PlaySound(WavFile, 0&, SND_ASYNC Or SND_FILENAME)
Else
Call PlaySound(WavFile, 0&, SND_SYNC Or SND_FILENAME)
End If
End Sub
[/vba]
Thanks to both of you.
I managed to get something to work. However in some cases I have to check for > and sometimes for <, and I didn't want the sound to keep playing if the condition was still met.
I decided to use a conditionnal formatting (so that others will be able to change the conditions), and to emit one sound only once when a cell BECOMES light blue. Here is my code.
[vba]
Option Explicit
Const SND_SYNC = &H0
Const SND_ASYNC = &H1
Const SND_FILENAME = &H20000
Private Declare Function PlaySound Lib "winmm.dll" Alias "PlaySoundA" _
(ByVal lpszName As String, _
ByVal hModule As Long, _
ByVal dwFlags As Long) As Long
Dim Val1 As Integer
Private Sub Worksheet_Activate()
Val1 = Range("C4").Interior.ColorIndex
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
With Target
Select Case .Address(0, 0)
Case "C4"
If Val1 = 2 And .Interior.ColorIndex = 8 Then PlayWAVFile "chimes.wav"
End Select
End With
Val1 = Range("C4").Interior.ColorIndex
ws_exit:
Application.EnableEvents = True
End Sub
Sub PlayWAVFile(WavFile As String, Optional Async As Boolean = True)
WavFile = "C:\Windows\Media\" & WavFile
If Async Then
Call PlaySound(WavFile, 0&, SND_ASYNC Or SND_FILENAME)
Else
Call PlaySound(WavFile, 0&, SND_SYNC Or SND_FILENAME)
End If
End Sub
[/vba]
But it is not working, and when I add the line Cells(1, 1) = Range("C4").Interior.ColorIndex in Worksheet_Activate() I get the value -4172 in A1. This is weird since the color values, 2 and 8, were found through a recorded macro. I really can't figure out what the problem might be.
Hi Jungix
A conditionally formatted cell does not have the interior.colorindex property. Have a look at this KB item.
http://vbaexpress.com/kb/getarticle.php?kb_id=190
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
Thanks Malcolm that enabled me to make it work for one worksheet.
However, I copied the code into another worksheet (in the same document), and now it's not working anymore in any of the cells.
I'm not familiar with Option Explicit and these kind of things. Is the fact that I declare everything twice (with the same name) an issue?
Can you post your revised code?
BTW With Option Explicit, if you click on Debug/Compile VBAProject, this will throw up any compilation errors.
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
I have exactly the same in sheet1 and sheet2
[VBA]
Option Explicit
Const SND_SYNC = &H0
Const SND_ASYNC = &H1
Const SND_FILENAME = &H20000
Private Declare Function PlaySound Lib "winmm.dll" Alias "PlaySoundA" _
(ByVal lpszName As String, _
ByVal hModule As Long, _
ByVal dwFlags As Long) As Long
Dim Val1 As Integer
'Dim seuil1 As Double
Dim Val2 As Double
Dim Val3 As Double
Dim Val4 As Double
Dim Val5 As Double
Dim Val6 As Double
Dim Val7 As Double
Dim Val8 As Double
Dim Val9 As Double
Dim Val10 As Double
Dim Val11 As Double
Dim Val12 As Double
Dim Val13 As Double
Dim Val14 As Double
Dim Val15 As Double
Dim Val16 As Double
Dim Val17 As Double
Dim Val18 As Double
Dim Val19 As Double
Dim Val20 As Double
Dim Val21 As Double
Dim Val22 As Double
Dim Val23 As Double
Dim Val24 As Double
Dim Val25 As Double
Dim Val26 As Double
Dim Val27 As Double
Dim Val28 As Double
Dim Val29 As Double
Dim Val30 As Double
Dim Val31 As Double
'Dim seuil2 As Double
'Dim seuil3 As Double
'Dim seuil4 As Double
'Dim seuil5 As Double
'Dim seuil6 As Double
'Dim seuil7 As Double
'Dim seuil8 As Double
'Dim seuil9 As Double
'Dim seuil10 As Double
'Dim seuil11 As Double
'Dim seuil12 As Double
'Dim seuil13 As Double
'Dim seuil14 As Double
'Dim seuil15 As Double
'Dim seuil16 As Double
'Dim seuil17 As Double
'Dim seuil18 As Double
'Dim seuil19 As Double
'Dim seuil20 As Double
'Dim seuil21 As Double
'Dim seuil22 As Double
'Dim seuil23 As Double
'Dim seuil24 As Double
'Dim seuil25 As Double
'Dim seuil26 As Double
'Dim seuil27 As Double
'Dim seuil28 As Double
'Dim seuil29 As Double
'Dim seuil30 As Double
'Dim seuil31 As Double
Private Sub Worksheet_Activate()
Val1 = ConditionalColor(Range("C4"), "")
'seuil1 = Right(Range("A4"), Len(Range("A4")) - 1)
Val2 = ConditionalColor(Range("C8"), "")
'seuil2 = Right(Range("A8"), Len(Range("A8")) - 1)
Val3 = ConditionalColor(Range("C12"), "")
'seuil3 = Right(Range("A12"), Len(Range("A12")) - 1)
Val4 = ConditionalColor(Range("C16"), "")
'seuil4 = Right(Range("A16"), Len(Range("A16")) - 1)
Val5 = ConditionalColor(Range("C20"), "")
'seuil5 = Right(Range("A20"), Len(Range("A20")) - 1)
Val6 = ConditionalColor(Range("C24"), "")
'seuil6 = Right(Range("A24"), Len(Range("A24")) - 1)
Val7 = ConditionalColor(Range("C28"), "")
'seuil7 = Right(Range("A28"), Len(Range("A28")) - 1)
Val8 = ConditionalColor(Range("C32"), "")
'seuil8 = Right(Range("A32"), Len(Range("A32")) - 1)
Val9 = ConditionalColor(Range("C36"), "")
'seuil9 = Right(Range("A36"), Len(Range("A36")) - 1)
Val10 = ConditionalColor(Range("C40"), "")
'seuil10 = Right(Range("A40"), Len(Range("A40")) - 1)
Val11 = ConditionalColor(Range("C44"), "")
'seuil11 = Right(Range("A44"), Len(Range("A44")) - 1)
Val12 = ConditionalColor(Range("C48"), "")
'seuil12 = Right(Range("A48"), Len(Range("A48")) - 1)
Val13 = ConditionalColor(Range("C52"), "")
'seuil13 = Right(Range("A52"), Len(Range("A52")) - 1)
Val14 = ConditionalColor(Range("C56"), "")
'seuil14 = Right(Range("A56"), Len(Range("A56")) - 1)
Val15 = ConditionalColor(Range("C60"), "")
'seuil15 = Right(Range("A60"), Len(Range("A60")) - 1)
Val16 = ConditionalColor(Range("C64"), "")
'seuil16 = Right(Range("A64"), Len(Range("A64")) - 1)
Val17 = ConditionalColor(Range("C68"), "")
'seuil17 = Right(Range("A68"), Len(Range("A68")) - 1)
Val18 = ConditionalColor(Range("C72"), "")
'seuil18 = Right(Range("A72"), Len(Range("A72")) - 1)
Val19 = ConditionalColor(Range("C76"), "")
'seuil19 = Right(Range("A76"), Len(Range("A76")) - 1)
Val20 = ConditionalColor(Range("C80"), "")
'seuil20 = Right(Range("A80"), Len(Range("A80")) - 1)
Val21 = ConditionalColor(Range("C84"), "")
'seuil21 = Right(Range("A84"), Len(Range("A84")) - 1)
Val22 = ConditionalColor(Range("C88"), "")
'seuil22 = Right(Range("A88"), Len(Range("A88")) - 1)
Val23 = ConditionalColor(Range("C92"), "")
'seuil23 = Right(Range("A92"), Len(Range("A92")) - 1)
Val24 = ConditionalColor(Range("C96"), "")
'seuil24 = Right(Range("A96"), Len(Range("A96")) - 1)
Val25 = ConditionalColor(Range("C100"), "")
'seuil25 = Right(Range("A100"), Len(Range("A100")) - 1)
Val26 = ConditionalColor(Range("C104"), "")
'seuil26 = Right(Range("A104"), Len(Range("A104")) - 1)
Val27 = ConditionalColor(Range("C108"), "")
'seuil27 = Right(Range("A108"), Len(Range("A108")) - 1)
Val28 = ConditionalColor(Range("C112"), "")
'seuil28 = Right(Range("A112"), Len(Range("A112")) - 1)
Val29 = ConditionalColor(Range("C116"), "")
'seuil29 = Right(Range("A116"), Len(Range("A116")) - 1)
Val30 = ConditionalColor(Range("C120"), "")
'seuil30 = Right(Range("A120"), Len(Range("A120")) - 1)
Val31 = ConditionalColor(Range("C124"), "")
'seuil31 = Right(Range("A124"), Len(Range("A124")) - 1)
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
With Target
Select Case .Address(0, 0)
Case "C4"
If Val1 = -4142 And ConditionalColor(Range("C4"), "") = 8 Then PlayWAVFile "chimes.wav"
'If Val1 >= seuil1 And .Value < seuil1 Then PlayWAVFile "chimes.wav"
Case "C8"
If Val2 = -4142 And ConditionalColor(Range("C8"), "") = 8 Then PlayWAVFile "chimes.wav"
'If Val2 >= seuil2 And .Value < seuil2 Then PlayWAVFile "chimes.wav"
Case "C12"
If Val3 = -4142 And ConditionalColor(Range("C12"), "") = 8 Then PlayWAVFile "chimes.wav"
'If Val3 >= seuil3 And .Value < seuil3 Then PlayWAVFile "chimes.wav"
Case "C16"
If Val4 = -4142 And ConditionalColor(Range("C16"), "") = 8 Then PlayWAVFile "chimes.wav"
'If Val4 >= seuil4 And .Value < seuil4 Then PlayWAVFile "chimes.wav"
Case "C20"
If Val5 = -4142 And ConditionalColor(Range("C20"), "") = 8 Then PlayWAVFile "chimes.wav"
'If Val5 >= seuil5 And .Value < seuil5 Then PlayWAVFile "chimes.wav"
Case "C24"
If Val6 = -4142 And ConditionalColor(Range("C24"), "") = 8 Then PlayWAVFile "chimes.wav"
'If Val6 >= seuil6 And .Value < seuil6 Then PlayWAVFile "chimes.wav"
Case "C28"
If Val7 = -4142 And ConditionalColor(Range("C28"), "") = 8 Then PlayWAVFile "chimes.wav"
'If Val7 >= seuil7 And .Value < seuil7 Then PlayWAVFile "chimes.wav"
Case "C32"
If Val8 = -4142 And ConditionalColor(Range("C32"), "") = 8 Then PlayWAVFile "chimes.wav"
'If Val8 >= seuil8 And .Value < seuil8 Then PlayWAVFile "chimes.wav"
Case "C36"
If Val9 = -4142 And ConditionalColor(Range("C36"), "") = 8 Then PlayWAVFile "chimes.wav"
'If Val9 >= seuil9 And .Value < seuil9 Then PlayWAVFile "chimes.wav"
Case "C40"
If Val10 = -4142 And ConditionalColor(Range("C40"), "") = 8 Then PlayWAVFile "chimes.wav"
'If Val10 >= seuil10 And .Value < seuil10 Then PlayWAVFile "chimes.wav"
Case "C44"
If Val11 = -4142 And ConditionalColor(Range("C44"), "") = 8 Then PlayWAVFile "chimes.wav"
'If Val11 >= seuil11 And .Value < seuil11 Then PlayWAVFile "chimes.wav"
Case "C48"
If Val12 = -4142 And ConditionalColor(Range("C48"), "") = 8 Then PlayWAVFile "chimes.wav"
'If Val12 >= seuil12 And .Value < seuil12 Then PlayWAVFile "chimes.wav"
Case "C52"
If Val13 = -4142 And ConditionalColor(Range("C52"), "") = 8 Then PlayWAVFile "chimes.wav"
'If Val13 >= seuil13 And .Value < seuil13 Then PlayWAVFile "chimes.wav"
Case "C56"
If Val14 = -4142 And ConditionalColor(Range("C56"), "") = 8 Then PlayWAVFile "chimes.wav"
'If Val14 >= seuil14 And .Value < seuil14 Then PlayWAVFile "chimes.wav"
Case "C60"
If Val15 = -4142 And ConditionalColor(Range("C60"), "") = 8 Then PlayWAVFile "chimes.wav"
'If Val15 >= seuil15 And .Value < seuil15 Then PlayWAVFile "chimes.wav"
Case "C64"
If Val16 = -4142 And ConditionalColor(Range("C64"), "") = 8 Then PlayWAVFile "chimes.wav"
'If Val16 >= seuil16 And .Value < seuil16 Then PlayWAVFile "chimes.wav"
Case "C68"
If Val17 = -4142 And ConditionalColor(Range("C68"), "") = 8 Then PlayWAVFile "chimes.wav"
'If Val17 >= seuil17 And .Value < seuil17 Then PlayWAVFile "chimes.wav"
Case "C72"
If Val18 = -4142 And ConditionalColor(Range("C72"), "") = 8 Then PlayWAVFile "chimes.wav"
'If Val18 >= seuil18 And .Value < seuil18 Then PlayWAVFile "chimes.wav"
Case "C76"
If Val19 = -4142 And ConditionalColor(Range("C76"), "") = 8 Then PlayWAVFile "chimes.wav"
'If Val19 >= seuil19 And .Value < seuil19 Then PlayWAVFile "chimes.wav"
Case "C80"
If Val20 = -4142 And ConditionalColor(Range("C80"), "") = 8 Then PlayWAVFile "chimes.wav"
'If Val20 >= seuil20 And .Value < seuil20 Then PlayWAVFile "chimes.wav"
Case "C84"
If Val21 = -4142 And ConditionalColor(Range("C84"), "") = 8 Then PlayWAVFile "chimes.wav"
'If Val21 >= seuil21 And .Value < seuil21 Then PlayWAVFile "chimes.wav"
Case "C88"
If Val22 = -4142 And ConditionalColor(Range("C88"), "") = 8 Then PlayWAVFile "chimes.wav"
'If Val22 >= seuil22 And .Value < seuil22 Then PlayWAVFile "chimes.wav"
Case "C92"
If Val23 = -4142 And ConditionalColor(Range("C92"), "") = 8 Then PlayWAVFile "chimes.wav"
'If Val23 >= seuil23 And .Value < seuil23 Then PlayWAVFile "chimes.wav"
Case "C96"
If Val24 = -4142 And ConditionalColor(Range("C96"), "") = 8 Then PlayWAVFile "chimes.wav"
'If Val24 >= seuil24 And .Value < seuil24 Then PlayWAVFile "chimes.wav"
Case "C100"
If Val25 = -4142 And ConditionalColor(Range("C100"), "") = 8 Then PlayWAVFile "chimes.wav"
'If Val25 >= seuil25 And .Value < seuil25 Then PlayWAVFile "chimes.wav"
Case "C104"
If Val26 = -4142 And ConditionalColor(Range("C104"), "") = 8 Then PlayWAVFile "chimes.wav"
'If Val26 >= seuil26 And .Value < seuil26 Then PlayWAVFile "chimes.wav"
Case "C108"
If Val27 = -4142 And ConditionalColor(Range("C108"), "") = 8 Then PlayWAVFile "chimes.wav"
'If Val27 >= seuil27 And .Value < seuil27 Then PlayWAVFile "chimes.wav"
Case "C112"
If Val28 = -4142 And ConditionalColor(Range("C112"), "") = 8 Then PlayWAVFile "chimes.wav"
'If Val28 >= seuil28 And .Value < seuil28 Then PlayWAVFile "chimes.wav"
Case "C116"
If Val29 = -4142 And ConditionalColor(Range("C116"), "") = 8 Then PlayWAVFile "chimes.wav"
'If Val29 >= seuil29 And .Value < seuil29 Then PlayWAVFile "chimes.wav"
Case "C120"
If Val30 = -4142 And ConditionalColor(Range("C120"), "") = 8 Then PlayWAVFile "chimes.wav"
'If Val30 >= seuil30 And .Value < seuil30 Then PlayWAVFile "chimes.wav"
Case "C124"
If Val31 = -4142 And ConditionalColor(Range("C124"), "") = 8 Then PlayWAVFile "chimes.wav"
'If Val31 >= seuil31 And .Value < seuil31 Then PlayWAVFile "chimes.wav"
End Select
End With
Val1 = ConditionalColor(Range("C4"), "")
'seuil1 = Right(Range("A4"), Len(Range("A4")) - 1)
Val2 = ConditionalColor(Range("C8"), "")
'seuil2 = Right(Range("A8"), Len(Range("A8")) - 1)
Val3 = ConditionalColor(Range("C12"), "")
'seuil3 = Right(Range("A12"), Len(Range("A12")) - 1)
Val4 = ConditionalColor(Range("C16"), "")
'seuil4 = Right(Range("A16"), Len(Range("A16")) - 1)
Val5 = ConditionalColor(Range("C20"), "")
'seuil5 = Right(Range("A20"), Len(Range("A20")) - 1)
Val6 = ConditionalColor(Range("C24"), "")
'seuil6 = Right(Range("A24"), Len(Range("A24")) - 1)
Val7 = ConditionalColor(Range("C28"), "")
'seuil7 = Right(Range("A28"), Len(Range("A28")) - 1)
Val8 = ConditionalColor(Range("C32"), "")
'seuil8 = Right(Range("A32"), Len(Range("A32")) - 1)
Val9 = ConditionalColor(Range("C36"), "")
'seuil9 = Right(Range("A36"), Len(Range("A36")) - 1)
Val10 = ConditionalColor(Range("C40"), "")
'seuil10 = Right(Range("A40"), Len(Range("A40")) - 1)
Val11 = ConditionalColor(Range("C44"), "")
'seuil11 = Right(Range("A44"), Len(Range("A44")) - 1)
Val12 = ConditionalColor(Range("C48"), "")
'seuil12 = Right(Range("A48"), Len(Range("A48")) - 1)
Val13 = ConditionalColor(Range("C52"), "")
'seuil13 = Right(Range("A52"), Len(Range("A52")) - 1)
Val14 = ConditionalColor(Range("C56"), "")
'seuil14 = Right(Range("A56"), Len(Range("A56")) - 1)
Val15 = ConditionalColor(Range("C60"), "")
'seuil15 = Right(Range("A60"), Len(Range("A60")) - 1)
Val16 = ConditionalColor(Range("C64"), "")
'seuil16 = Right(Range("A64"), Len(Range("A64")) - 1)
Val17 = ConditionalColor(Range("C68"), "")
'seuil17 = Right(Range("A68"), Len(Range("A68")) - 1)
Val18 = ConditionalColor(Range("C72"), "")
'seuil18 = Right(Range("A72"), Len(Range("A72")) - 1)
Val19 = ConditionalColor(Range("C76"), "")
'seuil19 = Right(Range("A76"), Len(Range("A76")) - 1)
Val20 = ConditionalColor(Range("C80"), "")
'seuil20 = Right(Range("A80"), Len(Range("A80")) - 1)
Val21 = ConditionalColor(Range("C84"), "")
'seuil21 = Right(Range("A84"), Len(Range("A84")) - 1)
Val22 = ConditionalColor(Range("C88"), "")
'seuil22 = Right(Range("A88"), Len(Range("A88")) - 1)
Val23 = ConditionalColor(Range("C92"), "")
'seuil23 = Right(Range("A92"), Len(Range("A92")) - 1)
Val24 = ConditionalColor(Range("C96"), "")
'seuil24 = Right(Range("A96"), Len(Range("A96")) - 1)
Val25 = ConditionalColor(Range("C100"), "")
'seuil25 = Right(Range("A100"), Len(Range("A100")) - 1)
Val26 = ConditionalColor(Range("C104"), "")
'seuil26 = Right(Range("A104"), Len(Range("A104")) - 1)
Val27 = ConditionalColor(Range("C108"), "")
'seuil27 = Right(Range("A108"), Len(Range("A108")) - 1)
Val28 = ConditionalColor(Range("C112"), "")
'seuil28 = Right(Range("A112"), Len(Range("A112")) - 1)
Val29 = ConditionalColor(Range("C116"), "")
'seuil29 = Right(Range("A116"), Len(Range("A116")) - 1)
Val30 = ConditionalColor(Range("C120"), "")
'seuil30 = Right(Range("A120"), Len(Range("A120")) - 1)
Val31 = ConditionalColor(Range("C124"), "")
'seuil31 = Right(Range("A124"), Len(Range("A124")) - 1)
ws_exit:
Application.EnableEvents = True
End Sub
Sub PlayWAVFile(WavFile As String, Optional Async As Boolean = True)
WavFile = "C:\Windows\Media\" & WavFile
If Async Then
Call PlaySound(WavFile, 0&, SND_ASYNC Or SND_FILENAME)
Else
Call PlaySound(WavFile, 0&, SND_SYNC Or SND_FILENAME)
End If
End Sub
Public Function ConditionalColor(rg As Range, FormatType As String) As Long
'Returns the color index (either font or interior) of the first cell in range rg. If no _
conditional format conditions apply, Then returns the regular color of the cell. _
FormatType Is either "Font" Or "Interior"
Dim cel As Range
Dim tmp As Variant
Dim boo As Boolean
Dim frmla As String, frmlaR1C1 As String, frmlaA1 As String
Dim i As Long
'Application.Volatile 'This statement required if Conditional Formatting for rg is determined by the _
value of other cells
Set cel = rg.Cells(1, 1)
Select Case Left(LCase(FormatType), 1)
Case "f" 'Font color
ConditionalColor = cel.Font.ColorIndex
Case Else 'Interior or highlight color
ConditionalColor = cel.Interior.ColorIndex
End Select
If cel.FormatConditions.Count > 0 Then
'On Error Resume Next
With cel.FormatConditions
For i = 1 To .Count 'Loop through the three possible format conditions for each cell
frmla = .Item(i).Formula1
If Left(frmla, 1) = "=" Then 'If "Formula Is", then evaluate if it is True
'Conditional Formatting is interpreted relative to the active cell. _
This cause the wrong results If the formula isn 't restated relative to the cell containing the _
Conditional Formatting--hence the workaround using ConvertFormula twice In a row. _
If the Function were Not called using a worksheet formula, you could just activate the cell instead.
frmlaR1C1 = Application.ConvertFormula(frmla, xlA1, xlR1C1, , ActiveCell)
frmlaA1 = Application.ConvertFormula(frmlaR1C1, xlR1C1, xlA1, xlAbsolute, cel)
boo = Application.Evaluate(frmlaA1)
Else 'If "Value Is", then identify the type of comparison operator and build comparison formula
Select Case .Item(i).Operator
Case xlEqual ' = x
frmla = cel & "=" & .Item(i).Formula1
Case xlNotEqual ' <> x
frmla = cel & "<>" & .Item(i).Formula1
Case xlBetween 'x <= cel <= y
frmla = "AND(" & .Item(i).Formula1 & "<=" & cel & "," & cel & "<=" & .Item(i).Formula2 & ")"
Case xlNotBetween 'x > cel or cel > y
frmla = "OR(" & .Item(i).Formula1 & ">" & cel & "," & cel & ">" & .Item(i).Formula2 & ")"
Case xlLess ' < x
frmla = cel & "<" & .Item(i).Formula1
Case xlLessEqual ' <= x
frmla = cel & "<=" & .Item(i).Formula1
Case xlGreater ' > x
frmla = cel & ">" & .Item(i).Formula1
Case xlGreaterEqual ' >= x
frmla = cel & ">=" & .Item(i).Formula1
End Select
boo = Application.Evaluate(frmla) 'Evaluate the "Value Is" comparison formula
End If
If boo Then 'If this Format Condition is satisfied
On Error Resume Next
Select Case Left(LCase(FormatType), 1)
Case "f" 'Font color
tmp = .Item(i).Font.ColorIndex
Case Else 'Interior or highlight color
tmp = .Item(i).Interior.ColorIndex
End Select
If Err = 0 Then ConditionalColor = tmp
Err.Clear
On Error GoTo 0
Exit For 'Since Format Condition is satisfied, exit the inner loop
End If
Next i
End With
End If
End Function
Sub NonConditionalFormatting()
Dim cel As Range
Application.ScreenUpdating = False
'Remove conditional formatting from entire worksheet
'For Each cel In ActiveSheet.UsedRange.SpecialCells(xlCellTypeAllFormatConditions)
For Each cel In Selection 'Remove conditional formatting from selected cells
If cel.FormatConditions.Count > 0 Then
cel.Interior.ColorIndex = ConditionalColor(cel, "Interior") 'Replace the interior (highlight) color
cel.Font.ColorIndex = ConditionalColor(cel, "Font") 'Replace the font color
cel.FormatConditions.Delete 'Delete all the Format Conditions for this cell
End If
Next cel
Application.ScreenUpdating = True
End Sub
[/VBA]
You can only have one copy of the subs called by the worksheet change events, so these have to be placed in a standard module.
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
It was still not working so I removed the code from one worksheet altogether and decided to keep it in only 1 worksheet but it's not working anymore
I just added one slightly modification in ConditionalColor about boo in case the value in the cell is empty. This is what I have now in worksheet1 and I'm getting crazy because I can't understand why it doesn't work.
[VBA]
Option Explicit
Const SND_SYNC = &H0
Const SND_ASYNC = &H1
Const SND_FILENAME = &H20000
Private Declare Function PlaySound Lib "winmm.dll" Alias "PlaySoundA" _
(ByVal lpszName As String, _
ByVal hModule As Long, _
ByVal dwFlags As Long) As Long
Dim Val1 As Integer
'Dim seuil1 As Double
Dim Val2 As Double
Dim Val3 As Double
Dim Val4 As Double
Dim Val5 As Double
Dim Val6 As Double
Dim Val7 As Double
Dim Val8 As Double
Dim Val9 As Double
Dim Val10 As Double
Dim Val11 As Double
Dim Val12 As Double
Dim Val13 As Double
Dim Val14 As Double
Dim Val15 As Double
Dim Val16 As Double
Dim Val17 As Double
Dim Val18 As Double
Dim Val19 As Double
Dim Val20 As Double
Dim Val21 As Double
Dim Val22 As Double
Dim Val23 As Double
Dim Val24 As Double
Dim Val25 As Double
Dim Val26 As Double
Dim Val27 As Double
Dim Val28 As Double
Dim Val29 As Double
Dim Val30 As Double
Dim Val31 As Double
'Dim seuil2 As Double
'Dim seuil3 As Double
'Dim seuil4 As Double
'Dim seuil5 As Double
'Dim seuil6 As Double
'Dim seuil7 As Double
'Dim seuil8 As Double
'Dim seuil9 As Double
'Dim seuil10 As Double
'Dim seuil11 As Double
'Dim seuil12 As Double
'Dim seuil13 As Double
'Dim seuil14 As Double
'Dim seuil15 As Double
'Dim seuil16 As Double
'Dim seuil17 As Double
'Dim seuil18 As Double
'Dim seuil19 As Double
'Dim seuil20 As Double
'Dim seuil21 As Double
'Dim seuil22 As Double
'Dim seuil23 As Double
'Dim seuil24 As Double
'Dim seuil25 As Double
'Dim seuil26 As Double
'Dim seuil27 As Double
'Dim seuil28 As Double
'Dim seuil29 As Double
'Dim seuil30 As Double
'Dim seuil31 As Double
Private Sub Worksheet_Activate()
Val1 = ConditionalColor(Range("C4"), "")
'seuil1 = Right(Range("A4"), Len(Range("A4")) - 1)
Val2 = ConditionalColor(Range("C8"), "")
'seuil2 = Right(Range("A8"), Len(Range("A8")) - 1)
Val3 = ConditionalColor(Range("C12"), "")
'seuil3 = Right(Range("A12"), Len(Range("A12")) - 1)
Val4 = ConditionalColor(Range("C16"), "")
'seuil4 = Right(Range("A16"), Len(Range("A16")) - 1)
Val5 = ConditionalColor(Range("C20"), "")
'seuil5 = Right(Range("A20"), Len(Range("A20")) - 1)
Val6 = ConditionalColor(Range("C24"), "")
'seuil6 = Right(Range("A24"), Len(Range("A24")) - 1)
Val7 = ConditionalColor(Range("C28"), "")
'seuil7 = Right(Range("A28"), Len(Range("A28")) - 1)
Val8 = ConditionalColor(Range("C32"), "")
'seuil8 = Right(Range("A32"), Len(Range("A32")) - 1)
Val9 = ConditionalColor(Range("C36"), "")
'seuil9 = Right(Range("A36"), Len(Range("A36")) - 1)
Val10 = ConditionalColor(Range("C40"), "")
'seuil10 = Right(Range("A40"), Len(Range("A40")) - 1)
Val11 = ConditionalColor(Range("C44"), "")
'seuil11 = Right(Range("A44"), Len(Range("A44")) - 1)
Val12 = ConditionalColor(Range("C48"), "")
'seuil12 = Right(Range("A48"), Len(Range("A48")) - 1)
Val13 = ConditionalColor(Range("C52"), "")
'seuil13 = Right(Range("A52"), Len(Range("A52")) - 1)
Val14 = ConditionalColor(Range("C56"), "")
'seuil14 = Right(Range("A56"), Len(Range("A56")) - 1)
Val15 = ConditionalColor(Range("C60"), "")
'seuil15 = Right(Range("A60"), Len(Range("A60")) - 1)
Val16 = ConditionalColor(Range("C64"), "")
'seuil16 = Right(Range("A64"), Len(Range("A64")) - 1)
Val17 = ConditionalColor(Range("C68"), "")
'seuil17 = Right(Range("A68"), Len(Range("A68")) - 1)
Val18 = ConditionalColor(Range("C72"), "")
'seuil18 = Right(Range("A72"), Len(Range("A72")) - 1)
Val19 = ConditionalColor(Range("C76"), "")
'seuil19 = Right(Range("A76"), Len(Range("A76")) - 1)
Val20 = ConditionalColor(Range("C80"), "")
'seuil20 = Right(Range("A80"), Len(Range("A80")) - 1)
Val21 = ConditionalColor(Range("C84"), "")
'seuil21 = Right(Range("A84"), Len(Range("A84")) - 1)
Val22 = ConditionalColor(Range("C88"), "")
'seuil22 = Right(Range("A88"), Len(Range("A88")) - 1)
Val23 = ConditionalColor(Range("C92"), "")
'seuil23 = Right(Range("A92"), Len(Range("A92")) - 1)
Val24 = ConditionalColor(Range("C96"), "")
'seuil24 = Right(Range("A96"), Len(Range("A96")) - 1)
Val25 = ConditionalColor(Range("C100"), "")
'seuil25 = Right(Range("A100"), Len(Range("A100")) - 1)
Val26 = ConditionalColor(Range("C104"), "")
'seuil26 = Right(Range("A104"), Len(Range("A104")) - 1)
Val27 = ConditionalColor(Range("C108"), "")
'seuil27 = Right(Range("A108"), Len(Range("A108")) - 1)
Val28 = ConditionalColor(Range("C112"), "")
'seuil28 = Right(Range("A112"), Len(Range("A112")) - 1)
Val29 = ConditionalColor(Range("C116"), "")
'seuil29 = Right(Range("A116"), Len(Range("A116")) - 1)
Val30 = ConditionalColor(Range("C120"), "")
'seuil30 = Right(Range("A120"), Len(Range("A120")) - 1)
Val31 = ConditionalColor(Range("C124"), "")
'seuil31 = Right(Range("A124"), Len(Range("A124")) - 1)
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
With Target
Select Case .Address(0, 0)
Case "C4"
If Val1 = -4142 And ConditionalColor(Range("C4"), "") = 8 Then PlayWAVFile "chimes.wav"
'If Val1 >= seuil1 And .Value < seuil1 Then PlayWAVFile "chimes.wav"
Case "C8"
If Val2 = -4142 And ConditionalColor(Range("C8"), "") = 8 Then PlayWAVFile "chimes.wav"
'If Val2 >= seuil2 And .Value < seuil2 Then PlayWAVFile "chimes.wav"
Case "C12"
If Val3 = -4142 And ConditionalColor(Range("C12"), "") = 8 Then PlayWAVFile "chimes.wav"
'If Val3 >= seuil3 And .Value < seuil3 Then PlayWAVFile "chimes.wav"
Case "C16"
If Val4 = -4142 And ConditionalColor(Range("C16"), "") = 8 Then PlayWAVFile "chimes.wav"
'If Val4 >= seuil4 And .Value < seuil4 Then PlayWAVFile "chimes.wav"
Case "C20"
If Val5 = -4142 And ConditionalColor(Range("C20"), "") = 8 Then PlayWAVFile "chimes.wav"
'If Val5 >= seuil5 And .Value < seuil5 Then PlayWAVFile "chimes.wav"
Case "C24"
If Val6 = -4142 And ConditionalColor(Range("C24"), "") = 8 Then PlayWAVFile "chimes.wav"
'If Val6 >= seuil6 And .Value < seuil6 Then PlayWAVFile "chimes.wav"
Case "C28"
If Val7 = -4142 And ConditionalColor(Range("C28"), "") = 8 Then PlayWAVFile "chimes.wav"
'If Val7 >= seuil7 And .Value < seuil7 Then PlayWAVFile "chimes.wav"
Case "C32"
If Val8 = -4142 And ConditionalColor(Range("C32"), "") = 8 Then PlayWAVFile "chimes.wav"
'If Val8 >= seuil8 And .Value < seuil8 Then PlayWAVFile "chimes.wav"
Case "C36"
If Val9 = -4142 And ConditionalColor(Range("C36"), "") = 8 Then PlayWAVFile "chimes.wav"
'If Val9 >= seuil9 And .Value < seuil9 Then PlayWAVFile "chimes.wav"
Case "C40"
If Val10 = -4142 And ConditionalColor(Range("C40"), "") = 8 Then PlayWAVFile "chimes.wav"
'If Val10 >= seuil10 And .Value < seuil10 Then PlayWAVFile "chimes.wav"
Case "C44"
If Val11 = -4142 And ConditionalColor(Range("C44"), "") = 8 Then PlayWAVFile "chimes.wav"
'If Val11 >= seuil11 And .Value < seuil11 Then PlayWAVFile "chimes.wav"
Case "C48"
If Val12 = -4142 And ConditionalColor(Range("C48"), "") = 8 Then PlayWAVFile "chimes.wav"
'If Val12 >= seuil12 And .Value < seuil12 Then PlayWAVFile "chimes.wav"
Case "C52"
If Val13 = -4142 And ConditionalColor(Range("C52"), "") = 8 Then PlayWAVFile "chimes.wav"
'If Val13 >= seuil13 And .Value < seuil13 Then PlayWAVFile "chimes.wav"
Case "C56"
If Val14 = -4142 And ConditionalColor(Range("C56"), "") = 8 Then PlayWAVFile "chimes.wav"
'If Val14 >= seuil14 And .Value < seuil14 Then PlayWAVFile "chimes.wav"
Case "C60"
If Val15 = -4142 And ConditionalColor(Range("C60"), "") = 8 Then PlayWAVFile "chimes.wav"
'If Val15 >= seuil15 And .Value < seuil15 Then PlayWAVFile "chimes.wav"
Case "C64"
If Val16 = -4142 And ConditionalColor(Range("C64"), "") = 8 Then PlayWAVFile "chimes.wav"
'If Val16 >= seuil16 And .Value < seuil16 Then PlayWAVFile "chimes.wav"
Case "C68"
If Val17 = -4142 And ConditionalColor(Range("C68"), "") = 8 Then PlayWAVFile "chimes.wav"
'If Val17 >= seuil17 And .Value < seuil17 Then PlayWAVFile "chimes.wav"
Case "C72"
If Val18 = -4142 And ConditionalColor(Range("C72"), "") = 8 Then PlayWAVFile "chimes.wav"
'If Val18 >= seuil18 And .Value < seuil18 Then PlayWAVFile "chimes.wav"
Case "C76"
If Val19 = -4142 And ConditionalColor(Range("C76"), "") = 8 Then PlayWAVFile "chimes.wav"
'If Val19 >= seuil19 And .Value < seuil19 Then PlayWAVFile "chimes.wav"
Case "C80"
If Val20 = -4142 And ConditionalColor(Range("C80"), "") = 8 Then PlayWAVFile "chimes.wav"
'If Val20 >= seuil20 And .Value < seuil20 Then PlayWAVFile "chimes.wav"
Case "C84"
If Val21 = -4142 And ConditionalColor(Range("C84"), "") = 8 Then PlayWAVFile "chimes.wav"
'If Val21 >= seuil21 And .Value < seuil21 Then PlayWAVFile "chimes.wav"
Case "C88"
If Val22 = -4142 And ConditionalColor(Range("C88"), "") = 8 Then PlayWAVFile "chimes.wav"
'If Val22 >= seuil22 And .Value < seuil22 Then PlayWAVFile "chimes.wav"
Case "C92"
If Val23 = -4142 And ConditionalColor(Range("C92"), "") = 8 Then PlayWAVFile "chimes.wav"
'If Val23 >= seuil23 And .Value < seuil23 Then PlayWAVFile "chimes.wav"
Case "C96"
If Val24 = -4142 And ConditionalColor(Range("C96"), "") = 8 Then PlayWAVFile "chimes.wav"
'If Val24 >= seuil24 And .Value < seuil24 Then PlayWAVFile "chimes.wav"
Case "C100"
If Val25 = -4142 And ConditionalColor(Range("C100"), "") = 8 Then PlayWAVFile "chimes.wav"
'If Val25 >= seuil25 And .Value < seuil25 Then PlayWAVFile "chimes.wav"
Case "C104"
If Val26 = -4142 And ConditionalColor(Range("C104"), "") = 8 Then PlayWAVFile "chimes.wav"
'If Val26 >= seuil26 And .Value < seuil26 Then PlayWAVFile "chimes.wav"
Case "C108"
If Val27 = -4142 And ConditionalColor(Range("C108"), "") = 8 Then PlayWAVFile "chimes.wav"
'If Val27 >= seuil27 And .Value < seuil27 Then PlayWAVFile "chimes.wav"
Case "C112"
If Val28 = -4142 And ConditionalColor(Range("C112"), "") = 8 Then PlayWAVFile "chimes.wav"
'If Val28 >= seuil28 And .Value < seuil28 Then PlayWAVFile "chimes.wav"
Case "C116"
If Val29 = -4142 And ConditionalColor(Range("C116"), "") = 8 Then PlayWAVFile "chimes.wav"
'If Val29 >= seuil29 And .Value < seuil29 Then PlayWAVFile "chimes.wav"
Case "C120"
If Val30 = -4142 And ConditionalColor(Range("C120"), "") = 8 Then PlayWAVFile "chimes.wav"
'If Val30 >= seuil30 And .Value < seuil30 Then PlayWAVFile "chimes.wav"
Case "C124"
If Val31 = -4142 And ConditionalColor(Range("C124"), "") = 8 Then PlayWAVFile "chimes.wav"
'If Val31 >= seuil31 And .Value < seuil31 Then PlayWAVFile "chimes.wav"
End Select
End With
Val1 = ConditionalColor(Range("C4"), "")
'seuil1 = Right(Range("A4"), Len(Range("A4")) - 1)
Val2 = ConditionalColor(Range("C8"), "")
'seuil2 = Right(Range("A8"), Len(Range("A8")) - 1)
Val3 = ConditionalColor(Range("C12"), "")
'seuil3 = Right(Range("A12"), Len(Range("A12")) - 1)
Val4 = ConditionalColor(Range("C16"), "")
'seuil4 = Right(Range("A16"), Len(Range("A16")) - 1)
Val5 = ConditionalColor(Range("C20"), "")
'seuil5 = Right(Range("A20"), Len(Range("A20")) - 1)
Val6 = ConditionalColor(Range("C24"), "")
'seuil6 = Right(Range("A24"), Len(Range("A24")) - 1)
Val7 = ConditionalColor(Range("C28"), "")
'seuil7 = Right(Range("A28"), Len(Range("A28")) - 1)
Val8 = ConditionalColor(Range("C32"), "")
'seuil8 = Right(Range("A32"), Len(Range("A32")) - 1)
Val9 = ConditionalColor(Range("C36"), "")
'seuil9 = Right(Range("A36"), Len(Range("A36")) - 1)
Val10 = ConditionalColor(Range("C40"), "")
'seuil10 = Right(Range("A40"), Len(Range("A40")) - 1)
Val11 = ConditionalColor(Range("C44"), "")
'seuil11 = Right(Range("A44"), Len(Range("A44")) - 1)
Val12 = ConditionalColor(Range("C48"), "")
'seuil12 = Right(Range("A48"), Len(Range("A48")) - 1)
Val13 = ConditionalColor(Range("C52"), "")
'seuil13 = Right(Range("A52"), Len(Range("A52")) - 1)
Val14 = ConditionalColor(Range("C56"), "")
'seuil14 = Right(Range("A56"), Len(Range("A56")) - 1)
Val15 = ConditionalColor(Range("C60"), "")
'seuil15 = Right(Range("A60"), Len(Range("A60")) - 1)
Val16 = ConditionalColor(Range("C64"), "")
'seuil16 = Right(Range("A64"), Len(Range("A64")) - 1)
Val17 = ConditionalColor(Range("C68"), "")
'seuil17 = Right(Range("A68"), Len(Range("A68")) - 1)
Val18 = ConditionalColor(Range("C72"), "")
'seuil18 = Right(Range("A72"), Len(Range("A72")) - 1)
Val19 = ConditionalColor(Range("C76"), "")
'seuil19 = Right(Range("A76"), Len(Range("A76")) - 1)
Val20 = ConditionalColor(Range("C80"), "")
'seuil20 = Right(Range("A80"), Len(Range("A80")) - 1)
Val21 = ConditionalColor(Range("C84"), "")
'seuil21 = Right(Range("A84"), Len(Range("A84")) - 1)
Val22 = ConditionalColor(Range("C88"), "")
'seuil22 = Right(Range("A88"), Len(Range("A88")) - 1)
Val23 = ConditionalColor(Range("C92"), "")
'seuil23 = Right(Range("A92"), Len(Range("A92")) - 1)
Val24 = ConditionalColor(Range("C96"), "")
'seuil24 = Right(Range("A96"), Len(Range("A96")) - 1)
Val25 = ConditionalColor(Range("C100"), "")
'seuil25 = Right(Range("A100"), Len(Range("A100")) - 1)
Val26 = ConditionalColor(Range("C104"), "")
'seuil26 = Right(Range("A104"), Len(Range("A104")) - 1)
Val27 = ConditionalColor(Range("C108"), "")
'seuil27 = Right(Range("A108"), Len(Range("A108")) - 1)
Val28 = ConditionalColor(Range("C112"), "")
'seuil28 = Right(Range("A112"), Len(Range("A112")) - 1)
Val29 = ConditionalColor(Range("C116"), "")
'seuil29 = Right(Range("A116"), Len(Range("A116")) - 1)
Val30 = ConditionalColor(Range("C120"), "")
'seuil30 = Right(Range("A120"), Len(Range("A120")) - 1)
Val31 = ConditionalColor(Range("C124"), "")
'seuil31 = Right(Range("A124"), Len(Range("A124")) - 1)
ws_exit:
Application.EnableEvents = True
End Sub
Public Sub PlayWAVFile(WavFile As String, Optional Async As Boolean = True)
WavFile = "C:\Windows\Media\" & WavFile
If Async Then
Call PlaySound(WavFile, 0&, SND_ASYNC Or SND_FILENAME)
Else
Call PlaySound(WavFile, 0&, SND_SYNC Or SND_FILENAME)
End If
End Sub
Public Function ConditionalColor(rg As Range, FormatType As String) As Long
'Returns the color index (either font or interior) of the first cell in range rg. If no _
conditional format conditions apply, Then returns the regular color of the cell. _
FormatType Is either "Font" Or "Interior"
Dim cel As Range
Dim tmp As Variant
Dim boo As Boolean
Dim frmla As String, frmlaR1C1 As String, frmlaA1 As String
Dim i As Long
'Application.Volatile 'This statement required if Conditional Formatting for rg is determined by the _
value of other cells
Set cel = rg.Cells(1, 1)
Select Case Left(LCase(FormatType), 1)
Case "f" 'Font color
ConditionalColor = cel.Font.ColorIndex
Case Else 'Interior or highlight color
ConditionalColor = cel.Interior.ColorIndex
End Select
If cel.FormatConditions.Count > 0 Then
'On Error Resume Next
With cel.FormatConditions
For i = 1 To .Count 'Loop through the three possible format conditions for each cell
frmla = .Item(i).Formula1
If Left(frmla, 1) = "=" Then 'If "Formula Is", then evaluate if it is True
'Conditional Formatting is interpreted relative to the active cell. _
This cause the wrong results If the formula isn 't restated relative to the cell containing the _
Conditional Formatting--hence the workaround using ConvertFormula twice In a row. _
If the Function were Not called using a worksheet formula, you could just activate the cell instead.
frmlaR1C1 = Application.ConvertFormula(frmla, xlA1, xlR1C1, , ActiveCell)
frmlaA1 = Application.ConvertFormula(frmlaR1C1, xlR1C1, xlA1, xlAbsolute, cel)
boo = Application.Evaluate(frmlaA1)
Else 'If "Value Is", then identify the type of comparison operator and build comparison formula
Select Case .Item(i).Operator
Case xlEqual ' = x
frmla = cel & "=" & .Item(i).Formula1
Case xlNotEqual ' <> x
frmla = cel & "<>" & .Item(i).Formula1
Case xlBetween 'x <= cel <= y
frmla = "AND(" & .Item(i).Formula1 & "<=" & cel & "," & cel & "<=" & .Item(i).Formula2 & ")"
Case xlNotBetween 'x > cel or cel > y
frmla = "OR(" & .Item(i).Formula1 & ">" & cel & "," & cel & ">" & .Item(i).Formula2 & ")"
Case xlLess ' < x
frmla = cel & "<" & .Item(i).Formula1
Case xlLessEqual ' <= x
frmla = cel & "<=" & .Item(i).Formula1
Case xlGreater ' > x
frmla = cel & ">" & .Item(i).Formula1
Case xlGreaterEqual ' >= x
frmla = cel & ">=" & .Item(i).Formula1
End Select
boo = False
On Error Resume Next
boo = Application.Evaluate(frmla) 'Evaluate the "Value Is" comparison formula
On Error GoTo 0
End If
If boo Then 'If this Format Condition is satisfied
On Error Resume Next
Select Case Left(LCase(FormatType), 1)
Case "f" 'Font color
tmp = .Item(i).Font.ColorIndex
Case Else 'Interior or highlight color
tmp = .Item(i).Interior.ColorIndex
End Select
If Err = 0 Then ConditionalColor = tmp
Err.Clear
On Error GoTo 0
Exit For 'Since Format Condition is satisfied, exit the inner loop
End If
Next i
End With
End If
End Function
Sub NonConditionalFormatting()
Dim cel As Range
Application.ScreenUpdating = False
'Remove conditional formatting from entire worksheet
'For Each cel In ActiveSheet.UsedRange.SpecialCells(xlCellTypeAllFormatConditions)
For Each cel In Selection 'Remove conditional formatting from selected cells
If cel.FormatConditions.Count > 0 Then
cel.Interior.ColorIndex = ConditionalColor(cel, "Interior") 'Replace the interior (highlight) color
cel.Font.ColorIndex = ConditionalColor(cel, "Font") 'Replace the font color
cel.FormatConditions.Delete 'Delete all the Format Conditions for this cell
End If
Next cel
Application.ScreenUpdating = True
End Sub
[/VBA]
My mistake I wasn't aware that after a debug the worksheet_Change macro stopped working too.
Actually it works well now and I feel so dumb
Posting Permissions
Reply With Quote
