Hi dr,
If I make both values 2, your code marks the adjoining cells. As Simon says, you will always get MyColour = 3, regardless of cell value
Hi dr,
If I make both values 2, your code marks the adjoining cells. As Simon says, you will always get MyColour = 3, regardless of cell value
Hi MD,
The last vesion checks for CF op is "Greater than" and CF colour "=3" with an AND. Works for me no matter what the value or colour of the cells.
On a serious note, please post an example that doesn't work so I can understand.
thanks!
Try this, which should be 2007 compatible. It includes two tests for different CF both returning Red result
[vba]
Sub Test()
Dim Cel As Range
Dim Op As Long, Val As Long, Col As Long
For Each Cel In Range("A1:A20")
Op = Cel.FormatConditions(1).Operator
Val = Evaluate(Cel.FormatConditions(1).Formula1) 'Evaluation added for 2007
Col = Cel.FormatConditions(1).Interior.ColorIndex
If Col = 3 Then
Select Case Op
Case xlGreater
If Cel > Val Then
Cel.Offset(, 1) = "Greater"
Else
Cel.Offset(, 1) = "False"
End If
Case xlLess
If Cel < Val Then
Cel.Offset(, 1) = "Less"
Else
Cel.Offset(, 1) = "False"
End If
End Select
End If
Next
End Sub
[/vba]
Hi dr
This is my result (using 2007). I think there may be version issues here
rb, thanks again for looking at it, it seems, although it may be something with my sheet, that the code is looking at the CF settings rather than satisfied conditions, all my cells in the row being used have conditional formatting of xlgreater and Red, i only need to perform the action on a cell that has satisfied the conditions and the cell shows as red, for some reason. See the attached so you can see what i mean, it uses your code, slightly adapted to simply show the address of all that would be processed with your code, however it should only process J12, AB12 & AE12 as those are the only cells that have the CF satisfied on row 12.
Im using xl2003 here
Try this tweak to the code
[vba]
Option Explicit
Sub CF()
Dim cel As Range
Dim rng As Range
Dim myOp As Long
Dim myCell As Range
Dim myColour As Long, msg
Dim myVal As Long
Set rng = Sheets("24HR Summary").Range("A12:AJ12")
For Each cel In rng
On Error Resume Next
With cel.FormatConditions(1)
myColour = .Interior.ColorIndex
myOp = .Operator
myVal = Evaluate(.Formula1)
End With
If myColour = 3 And myOp = 5 And cel > myVal Then
msg = msg & vbNewLine & cel.Address
End If
myColour = 0
myOp = 0
Next cel
MsgBox msg
Set cel = Nothing
Set rng = Nothing
Set myCell = Nothing
End Sub
[/vba]
Malcolm, thats closer, it will show all the addresses of any CF'd cell that has a value greater than zero, but sadly still not right.
If you put a break on the msg line, what value do you see for myVal?
I meant to mention that myVal = Evaluate(.Formula1) evaluates to 0 in your test
here's what i used[VBA]Sub CF1()
Dim cel As Range
Dim rng As Range
Dim myOp As Long
Dim myCell As Range
Dim myColour As Long, msg
Dim myVal As Long
Set rng = Sheets("24HR Summary").Range("A12:AJ12")
For Each cel In rng
On Error Resume Next
With cel.FormatConditions(1)
myColour = .Interior.ColorIndex
myOp = .Operator
myVal = Evaluate(.Formula1)
End With
If myColour = 3 And myOp = 5 And cel > myVal Then
msg = msg & vbNewLine & cel.Address & " - " & myVal
End If
myColour = 0
myOp = 0
Next cel
MsgBox msg
Set cel = Nothing
Set rng = Nothing
Set myCell = Nothing
End Sub[VBA][/VBA][/VBA]
Try
[VBA]
myVal = .Formula1
[/VBA]
Simon,
I am a tad late to this paty and I haven't read all posts in the thread fully, but you simply seem to be testing if CF is set not if it is satisfied.
This seems to do what you want
[vba]
Sub CF()
Dim cel As Range
Dim rng As Range
Dim myOp As Long
Dim myCell As Range
Dim myColour As Long, msg
Set rng = Sheets("24HR Summary").Range("A12:AJ12")
For Each cel In rng
On Error Resume Next
If cel.FormatConditions.Count > 0 Then
If cel.Value > cel.FormatConditions(1).Formula1 Then
msg = msg & vbNewLine & cel.Address
End If
End If
myColour = 0
myOp = 0
Next cel
MsgBox msg
Set cel = Nothing
Set rng = Nothing
Set myCell = Nothing
End Sub
[/vba]
Hi Bob,
I'm getting the code to work on 2007 & 2003, but Simon is getting differing results. His test for Formula1 is coming up as 0, not 2 as expected.
I tried it on his workbook Malcolm, and it worked fine. Odd problem.
Hi Guys,
This might not the most ideal way (and again I'm a novice compare to you) but what if the code incorporates the same formula as the Conditional Format once it's determined the cell in question indeed has a Conditional Format applied to it (red interior in this case) i.e.
I ran this in Excel 2003 and the message box displayed the three Conditional Formatted cells (J12, AB12 and AE12).Code:Option Explicit
Sub CF()
Dim cel As Range
Dim rng As Range
Dim myOp As Long
Dim myCell As Range
Dim myColour As Long, msg
Set rng = Sheets("24HR Summary").Range("A12:AJ12")
For Each cel In rng
On Error Resume Next
With cel.FormatConditions(1)
myColour = .Interior.ColorIndex
'myOp = .Operator
End With
'If myColour = 3 And myOp = 5 Then
If myColour = 3 And cel.Value >= 2 Then
msg = msg & vbNewLine & cel.Address
End If
myColour = 0
myOp = 0
Next cel
MsgBox msg
Set cel = Nothing
Set rng = Nothing
Set myCell = Nothing
End Sub
HTH
Robert
That is the same way as I did it Robert, you just add a few more tests which would be useful if Simon adds extra conditions, for blue and green say, and you hardcode the value, I get it from Formula1..
Bob, yours worked perfect!, Malcolm, your latest adjustment myval= .formula1 also worked a treat, i swear i've tried a million diferent combinations for the formula and operators but all produced the results we were getting previously.....glad we got to the bottom of it as there is a case of stella at home that was going to be decimated!!! :)
Thanks again to Bob, Malcolm and rb!
Robert, thanks for sticking with it, i cannot hard code the value as in the real world each of the values will be different which is why i didn't do that in the first place.....far too much coding, but glad you found a solution too :)
I can't imagine, but is there a Excel setting that may affect that?Quote:
Originally Posted by mdmackillop
Hi Robert,
If you look at my sample in Post #23, i've used a slightly more complex test with differing colours and conditions