Conditional Format - Identifying Highlighted Cells

[pallaver]

A brief explanation:
I have 3 columns of data: X, Y, and Z. User inputs a high/low ranger
(i.e. 4 +3/-2, i.e. 2 to 7). If the number is outside of that range,
I have used conditional format to set the cell color to gray.

Now here's where I have a problem.
I'm attempting to add a fourth column, OK/NG column. If either of the
X, Y, or Z cells in a row are gray, then the fourth box also turns
gray and displays NG. Otherwise it will display OK (i.e. all XYZ
points are within spec).

I did a little test myself, and it seems as though the code below
works if you change the cell interior color on the excel spreadsheet,
but when the cell color is changed due to conditional formatting, the
macro reads the cell as a normal cell and displays OK for all entries.


For RowCounter = 1 to 10

If Sheets("Sheet1").Cells(RowCounter, 1).Interior.Pattern = xlSolid
Then
Sheets("Sheet1").Cells(RowCounter, 2).Value = "NG"
With Sheets("Sheet1").Cells(RowCounter, 2).Interior
.ColorIndex = 15
.Pattern = xlSolid
End With
Else
Sheets("Sheet1").Cells(RowCounter, 2).Value = "OK"
End If

Next RowCounter

------

Anybody else encountered this problem? Any way I can properly
identify a colored cell from conditional format in the macro?

Thanks. NP

 

[FSt1]

hi
xl vb cannot detect cells colored with conditional formating(CF) by the
usual code.
you have to test for CF.
somthing like this...
Sub detectconditions()
If Range("F4").FormatConditions(1).Interior.colorindex = 40 Then
MsgBox "has CF"
Else
MsgBox " no CF "
End If

you also need to know what contitions you're look for.
from your code, i don't understand why your are testing for patterns when
you should be testing for format condition color 15(gray)
also if your are adding CF, why are you trying to color the cell normally
instead of CF.
to add CF.....
cells(RowCounter, 1).FormatConditions.Add _
Type:=xlCellValue, Operator:=xlGreater, _
Formula1:="1"
Cells(RowCounter, 1).FormatConditions(1).Interior.colorindex = 15

Post back if questions.
regards
FSt1

:

 

[pallaver]

Hi FSt1,

Looking over your script, I modified mine. I made a mistake in
searching for pattern.

That being said, I still couldn't get it to work. Here's what I have
(modified with your addition):

-------------------------------------
(This is just for X, for Y and Z it's the same thing only
TeishutsuItemColumn +6 or +7, but I figured I would start with just X
to keep things simple to check)

Sheets("Variables").Cells(TeishutsuItemRow + (NumberingTeishutsuSheet
- 1), TeishutsuItemColumn + 5).Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlCellValue,
Operator:=xlGreater, Formula1:=XStanleyVariance + 0.008
Selection.FormatConditions(1).Interior.ColorIndex = 48
Selection.FormatConditions.Add Type:=xlCellValue,
Operator:=xlLess, Formula1:=XStanleyVariance - 0.008
With Selection.FormatConditions(2).Font
.Bold = False
.Italic = True
End With
Selection.FormatConditions(2).Interior.ColorIndex = 48
Selection.FormatConditions.Add Type:=xlCellValue,
Operator:=xlBetween, Formula1:=XStanleyVariance - 0.008,
Formula2:=XStanleyVariance + 0.008
Selection.FormatConditions(3).Interior.ColorIndex = xlNone

If Sheets("Variables").Cells(TeishutsuItemRow +
(NumberingTeishutsuSheet - 1), TeishutsuItemColumn +
5).FormatConditions(1).Interior.ColorIndex = 48 Then
MsgBox "has CF - NG"
Else
MsgBox " no CF - OK"
End If

-----------------

When I run this, I get "has CF - NG" even if all columns XYZ are left
alone, i.e. conditional formatting condition 3 for all three
variables.

Where'd I go wrong?
Thanks for your help.

Since, NP

 

[Héctor Miguel]

hi, (...) ?

try using the same "condition" on with the format would by executed with "simple" functions (i.e.)

- count if any/all/which/... of the cells in your X,Y,Z columns are "in/out of your high/low range"

hth,
hector.

__ OP __

 

[pallaver]

Hi Hector,

Neil here.
As for keeping things simple.... I'm trying too! Hehe.
As it turns out, each variable X, Y, and Z actually have different
inputed tolerances, thus I can't really make a general, simple
conditional format since it wouldn't apply to everything.

I will now try and make a new sheet and see if I can get the
formatconditions(1) statement to work. I think that's the best course
of action right now.

I'll post here if I find anything out. Otherwise, if I don't post, it
means I'm still stuck! lol

Since, Neil

 

[Héctor Miguel]

hi, Neil !

IMHO it will require the same (thinking) "effort" to find out *IF* any/all/which/... cell/s mets "a condition"
either by code or by (simple) worksheet-functions -?- so...

try being a little more "specific" regarding which the conditions/tolerances are for each of your X,Y,Z columns
(I mean, comment some details so *we* can reproduce the scenario to work with)

regards,
hector.

 

[pallaver]

Okay, I figured out one thing:

The following code below only tells me if that Conditional Format
exists for the cell. It does NOT tell me whether or not that CF
(Conditional Format) was used or implemented.

For instance, I made the following simple macro:

-------------------------------------
Range("A20").Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater,
_
Formula1:="2"
Selection.FormatConditions(1).Interior.ColorIndex = 44

If
Sheets("Sheet1").Range("A20").FormatConditions(1).Interior.ColorIndex
= 0 Then
Sheets("Sheet1").Range("B20").Value = "OK"
Else
Sheets("Sheet1").Range("B20").Value = "NG"
End If
-------------------------------------

In this particular case, the answer is NG. Now, if
FormatConditions(1).Interior.ColorIndex = 44 (i.e. change 0 to 44),
then "OK" gets displayed.

What this is doing then is checking to see if CF(1) is actually
setting the cell background value to 44.

What I want to confirm though is which formatcondition: (1), (2), or
(3) was used for a cell. Any ideas how to do this? Thanks, NP

 

[pallaver]

Hi Hector,

The reason why I want to use cell interior ColorIndex if possible to
determine NG/OK is because going the "simple" route may actually be
more difficult.

All XYZ numbers are brought in from a testing sheet which runs using
another macro and a measuring machine. I am constructing two
programs: The first creates the presentation sheet using a userform
where the excel user inputs in each X, Y, and Z tolerance. I have the
tolerances though input into phrases (i.e. 5 +1/-4). Sometimes
tolerances are carried over between points, sometimes not, so it can
look a little odd (i.e. point 1 X tolerance is the same as point 2,
but their Y tolerances are difference).

Regardless, I have a second program which inserts in new data. If
this new data can react to the same conditional formatting previously
set up, then all I have to do to determine whether or not the point is
NG/OK is find out if any of the three cells are colored. This is
easier than having to write a program which finds the tolerances for
each, dissects the number from the sentence, and in simple form asks
if the X number is within or outside of that tolerance, etc.

I believe the above is what you mean by simple, and yes, it is simple
if I could create the sheet to look more rudimentary, but this is the
way they wanted it to look.

My last resort option is probably just to make a hidden sheet which
lists quite simple a column for each tolerance, etc., and figures out
NG/OK from this sheet. Since it's hidden it won't be known/changed,
but figured it would be best to try and just keep everything in
code.

Hopefully this all makes sense.

-NP

 

[pallaver]

SOLUTION!!!

I figured out how to get VBA to give me which conditional format for
the cell (1, 2, or 3) was used! Since everything is done via VBA, I
know that 1/2 are NG situations, and 3 is OK, thus I can color my
desired OK/NG cell according to which CF for the cell was used.

My problems requires using ActiveCondition and GetStripped functions.
Very foreign to me, but I was able to find something on the web that
had a solution already which I modified. (That website below FYI).

http://www.experts-exchange.com/Sof...Office_Suites/MS_Office/Excel/Q_23501053.html

I put in the entire code (1 sub and 2 functions) into a module and
just used a call function with public variables to access it.

I hope this helps other people in the future - I know I for one use
the search function to look through archived posts here for answers
before posting anew.

----------------------------------------------------------------------------------
Option Explicit

Sub OKNGColors()
Dim rngCell As Range
Dim CFIndex As Long
Dim TeishutsuItemRow As Long
Dim TeishutsuItemColumn As Long

TeishutsuItemRow = 5
TeishutsuItemColumn = 1

For Each rngCell In Range(Cells(TeishutsuItemRow, TeishutsuItemColumn
+ 2), Cells(TeishutsuItemRow, TeishutsuItemColumn + 4))

CFIndex = ActiveCondition(rngCell)

MsgBox "CFIndex= " & (CFIndex)

Next rngCell

End Sub


Function ActiveCondition(rng As Range) As Integer
Dim Ndx As Long
Dim FC As FormatCondition
Dim Temp As Variant
Dim Temp2 As Variant

If rng.FormatConditions.Count = 0 Then
ActiveCondition = 0
Else
For Ndx = 1 To rng.FormatConditions.Count
Set FC = rng.FormatConditions(Ndx)
Select Case FC.Type
Case xlCellValue
Select Case FC.Operator
Case xlBetween
Temp = GetStrippedValue(FC.Formula1)
Temp2 = GetStrippedValue(FC.Formula2)
If IsNumeric(Temp) Then
If CDbl(rng.Value) >= CDbl(FC.Formula1) And _
CDbl(rng.Value) <= CDbl(FC.Formula2) Then
ActiveCondition = Ndx
Exit Function
End If
Else
If rng.Value >= Temp And _
rng.Value <= Temp2 Then
ActiveCondition = Ndx
Exit Function
End If
End If

Case xlGreater
Temp = GetStrippedValue(FC.Formula1)
If IsNumeric(Temp) Then
If CDbl(rng.Value) > CDbl(FC.Formula1) Then
ActiveCondition = Ndx
Exit Function
End If
Else
If rng.Value > Temp Then
ActiveCondition = Ndx
Exit Function
End If
End If

Case xlEqual
Temp = GetStrippedValue(FC.Formula1)
If IsNumeric(Temp) Then
If CDbl(rng.Value) = CDbl(FC.Formula1) Then
ActiveCondition = Ndx
Exit Function
End If
Else
If Temp = rng.Value Then
ActiveCondition = Ndx
Exit Function
End If
End If


Case xlGreaterEqual
Temp = GetStrippedValue(FC.Formula1)
If IsNumeric(Temp) Then
If CDbl(rng.Value) >= CDbl(FC.Formula1) Then
ActiveCondition = Ndx
Exit Function
End If
Else
If rng.Value >= Temp Then
ActiveCondition = Ndx
Exit Function
End If
End If


Case xlLess
Temp = GetStrippedValue(FC.Formula1)
If IsNumeric(Temp) Then
If CDbl(rng.Value) < CDbl(FC.Formula1) Then
ActiveCondition = Ndx
Exit Function
End If
Else
If rng.Value < Temp Then
ActiveCondition = Ndx
Exit Function
End If
End If

Case xlLessEqual
Temp = GetStrippedValue(FC.Formula1)
If IsNumeric(Temp) Then
If CDbl(rng.Value) <= CDbl(FC.Formula1) Then
ActiveCondition = Ndx
Exit Function
End If
Else
If rng.Value <= Temp Then
ActiveCondition = Ndx
Exit Function
End If
End If


Case xlNotEqual
Temp = GetStrippedValue(FC.Formula1)
If IsNumeric(Temp) Then
If CDbl(rng.Value) <> CDbl(FC.Formula1) Then
ActiveCondition = Ndx
Exit Function
End If
Else
If Temp <> rng.Value Then
ActiveCondition = Ndx
Exit Function
End If
End If

Case xlNotBetween
Temp = GetStrippedValue(FC.Formula1)
Temp2 = GetStrippedValue(FC.Formula2)
If IsNumeric(Temp) Then
If Not (CDbl(rng.Value) <= CDbl(FC.Formula1)) And _
(CDbl(rng.Value) >= CDbl(FC.Formula2)) Then
ActiveCondition = Ndx
Exit Function
End If
Else
If Not rng.Value <= Temp And _
rng.Value >= Temp2 Then
ActiveCondition = Ndx
Exit Function
End If
End If

Case Else
' weird stuff
End Select

Case xlExpression
If Application.Evaluate(FC.Formula1) Then
ActiveCondition = Ndx
Exit Function
End If

Case Else
Debug.Print "UNKNOWN TYPE"
End Select

Next Ndx

End If
ActiveCondition = 0
End Function

Function GetStrippedValue(CF As String) As String
Dim Temp As String
If InStr(1, CF, "=", vbTextCompare) Then
Temp = Mid(CF, 3, Len(CF) - 3)
If Left(Temp, 1) = "=" Then
Temp = Mid(Temp, 2)
End If
Else
Temp = CF
End If
GetStrippedValue = Temp
End Function

 

[pallaver]

Just in case people try as I did - The above code only works in Excel
97-2003.
I brought it home and tried it out on Excel 2007 and it did not work.