Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Colors of Cells With Conditional Formatting
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 211
Default Colors of Cells With Conditional Formatting
I have a code for selecting the cells with specific interior color be
selected as follows, however, how to add cells colored similarly but due to
conditional formatting, whether by the 1st, 2nd or any condition?

Sub SlctClrCel(CONTROL As IRibbonControl)
Dim CRange As Range
Dim A As Range
Dim B As Range
RETRY:
Set A = Application.InputBox("Select A Sample Cell With The Desired Interior
Color.", Type:=8)
Set B = Application.InputBox("Looking In Which Range?" & vbNewLine &
"Remember To Select Only The Necessary Cells", Type:=8)
For Each C In B
If C.Interior.ColorIndex = A.Interior.ColorIndex Then
If CRange Is Nothing Then
Set CRange = C
Else
Set CRange = Union(CRange, C)
End If
End If
Next
If Not CRange Is Nothing Then
CRange.Select
Else
MsgBox ("None Found!")
End If
End Sub

--
Thanx in advance,
Best Regards,

Faraz
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 8,520
Default Colors of Cells With Conditional Formatting
Hi Faraz

Try the below function to get the color index of a conditional formatted cell.

Function GetCFColorIndex(c As Range) As Variant
Dim intCount As Integer, FC As FormatCondition, blnMatch As Boolean
If c.Count < 1 Then Exit Function
For intCount = 1 To c.FormatConditions.Count
'Loop through each Contidional Formatting
Set FC = c.FormatConditions(intCount)
Application.Volatile
If FC.Type = 1 Then
'Handle Type1-xlExpression (If 'Cell Value Is')
Select Case FC.Operator
Case xlBetween '1
If c.Value = GetCFV(FC.Formula1, c) And c.Value _
<= GetCFV(FC.Formula2, c) Then blnMatch = True: Exit For
Case xlNotBetween '2
If c.Value < GetCFV(FC.Formula1, c) Or c.Value _
GetCFV(FC.Formula2, c) Then blnMatch = True: Exit For
Case xlEqual '3
If c.Value = GetCFV(FC.Formula1, c) Then blnMatch = True: Exit For
Case xlNotEqual '4
If c.Value < GetCFV(FC.Formula1, c) Then blnMatch = True: Exit For
Case xlGreater '5
If c.Value GetCFV(FC.Formula1, c) Then blnMatch = True: Exit For
Case xlGreaterEqual '6
If c.Value = GetCFV(FC.Formula1, c) Then blnMatch = True: Exit For
Case xlLess '7
If c.Value < GetCFV(FC.Formula1, c) Then blnMatch = True: Exit For
Case xlLessEqual '8
If c.Value <= GetCFV(FC.Formula1, c) Then blnMatch = True: Exit For
End Select
Else
'Handle Type2-xlExternal (If 'Formula Is')
If Evaluate(Application.ConvertFormula( _
Application.ConvertFormula(FC.Formula1, xlA1, xlR1C1), _
xlR1C1, xlA1, , c)) Then blnMatch = True: Exit For
End If
Next

If blnMatch Then GetCFColorIndex = FC.Interior.ColorIndex
End Function
'-------------------------------------------------------------------------------
Function GetCFV(strData As Variant, c As Range)
'Get text string or numeric from CF formula
If IsNumeric(strData) Then
GetCFV = CDbl(strData)
ElseIf InStr(strData, Chr(34)) Then
GetCFV = Mid(strData, 3, Len(strData) - 3)
Else
GetCFV = Range(Mid(Application.ConvertFormula( _
Application.ConvertFormula(strData, xlA1, xlR1C1), _
xlR1C1, xlA1, , c), 2))
End If
End Function
'-------------------------------------------------------------------------------


--
Jacob


"Faraz A. Qureshi" wrote:

I have a code for selecting the cells with specific interior color be
selected as follows, however, how to add cells colored similarly but due to
conditional formatting, whether by the 1st, 2nd or any condition?

Sub SlctClrCel(CONTROL As IRibbonControl)
Dim CRange As Range
Dim A As Range
Dim B As Range
RETRY:
Set A = Application.InputBox("Select A Sample Cell With The Desired Interior
Color.", Type:=8)
Set B = Application.InputBox("Looking In Which Range?" & vbNewLine &
"Remember To Select Only The Necessary Cells", Type:=8)
For Each C In B
If C.Interior.ColorIndex = A.Interior.ColorIndex Then
If CRange Is Nothing Then
Set CRange = C
Else
Set CRange = Union(CRange, C)
End If
End If
Next
If Not CRange Is Nothing Then
CRange.Select
Else
MsgBox ("None Found!")
End If
End Sub

--
Thanx in advance,
Best Regards,

Faraz
Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 211
Default Colors of Cells With Conditional Formatting
Nice 2 hear from u after such a longtime pal!
Sure had been busy myself!
By the way Your recommended Function no doubt presents a good way but the
result changes to "1" everytime I carryout a step after inserting the UDF
GetCFColorIndex, any reason?

By the way, instead of a function any idea for a procedure?
--
Thanx in advance,
Best Regards,

Faraz


"Jacob Skaria" wrote:

Hi Faraz

Try the below function to get the color index of a conditional formatted cell.

Function GetCFColorIndex(c As Range) As Variant
Dim intCount As Integer, FC As FormatCondition, blnMatch As Boolean
If c.Count < 1 Then Exit Function
For intCount = 1 To c.FormatConditions.Count
'Loop through each Contidional Formatting
Set FC = c.FormatConditions(intCount)
Application.Volatile
If FC.Type = 1 Then
'Handle Type1-xlExpression (If 'Cell Value Is')
Select Case FC.Operator
Case xlBetween '1
If c.Value = GetCFV(FC.Formula1, c) And c.Value _
<= GetCFV(FC.Formula2, c) Then blnMatch = True: Exit For
Case xlNotBetween '2
If c.Value < GetCFV(FC.Formula1, c) Or c.Value _
GetCFV(FC.Formula2, c) Then blnMatch = True: Exit For
Case xlEqual '3
If c.Value = GetCFV(FC.Formula1, c) Then blnMatch = True: Exit For
Case xlNotEqual '4
If c.Value < GetCFV(FC.Formula1, c) Then blnMatch = True: Exit For
Case xlGreater '5
If c.Value GetCFV(FC.Formula1, c) Then blnMatch = True: Exit For
Case xlGreaterEqual '6
If c.Value = GetCFV(FC.Formula1, c) Then blnMatch = True: Exit For
Case xlLess '7
If c.Value < GetCFV(FC.Formula1, c) Then blnMatch = True: Exit For
Case xlLessEqual '8
If c.Value <= GetCFV(FC.Formula1, c) Then blnMatch = True: Exit For
End Select
Else
'Handle Type2-xlExternal (If 'Formula Is')
If Evaluate(Application.ConvertFormula( _
Application.ConvertFormula(FC.Formula1, xlA1, xlR1C1), _
xlR1C1, xlA1, , c)) Then blnMatch = True: Exit For
End If
Next

If blnMatch Then GetCFColorIndex = FC.Interior.ColorIndex
End Function
'-------------------------------------------------------------------------------
Function GetCFV(strData As Variant, c As Range)
'Get text string or numeric from CF formula
If IsNumeric(strData) Then
GetCFV = CDbl(strData)
ElseIf InStr(strData, Chr(34)) Then
GetCFV = Mid(strData, 3, Len(strData) - 3)
Else
GetCFV = Range(Mid(Application.ConvertFormula( _
Application.ConvertFormula(strData, xlA1, xlR1C1), _
xlR1C1, xlA1, , c), 2))
End If
End Function
'-------------------------------------------------------------------------------


--
Jacob


"Faraz A. Qureshi" wrote:

I have a code for selecting the cells with specific interior color be
selected as follows, however, how to add cells colored similarly but due to
conditional formatting, whether by the 1st, 2nd or any condition?

Sub SlctClrCel(CONTROL As IRibbonControl)
Dim CRange As Range
Dim A As Range
Dim B As Range
RETRY:
Set A = Application.InputBox("Select A Sample Cell With The Desired Interior
Color.", Type:=8)
Set B = Application.InputBox("Looking In Which Range?" & vbNewLine &
"Remember To Select Only The Necessary Cells", Type:=8)
For Each C In B
If C.Interior.ColorIndex = A.Interior.ColorIndex Then
If CRange Is Nothing Then
Set CRange = C
Else
Set CRange = Union(CRange, C)
End If
End If
Next
If Not CRange Is Nothing Then
CRange.Select
Else
MsgBox ("None Found!")
End If
End Sub

--
Thanx in advance,
Best Regards,

Faraz
Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 8,520
Default Colors of Cells With Conditional Formatting
Paste both functions in a module and try with the macro.

The below will return the colorindex of cell D5. Try conditional formatting
cell D5 with fill color red and run the macro both with the condition and
without....If the cell is colored due to CF the macro will return the
colorindex applied...

Sub Macro1()
MsgBox GetCFColorIndex(Range("D5"))
End Sub


--
Jacob


"Faraz A. Qureshi" wrote:

Nice 2 hear from u after such a longtime pal!
Sure had been busy myself!
By the way Your recommended Function no doubt presents a good way but the
result changes to "1" everytime I carryout a step after inserting the UDF
GetCFColorIndex, any reason?

By the way, instead of a function any idea for a procedure?
--
Thanx in advance,
Best Regards,

Faraz


"Jacob Skaria" wrote:

Hi Faraz

Try the below function to get the color index of a conditional formatted cell.

Function GetCFColorIndex(c As Range) As Variant
Dim intCount As Integer, FC As FormatCondition, blnMatch As Boolean
If c.Count < 1 Then Exit Function
For intCount = 1 To c.FormatConditions.Count
'Loop through each Contidional Formatting
Set FC = c.FormatConditions(intCount)
Application.Volatile
If FC.Type = 1 Then
'Handle Type1-xlExpression (If 'Cell Value Is')
Select Case FC.Operator
Case xlBetween '1
If c.Value = GetCFV(FC.Formula1, c) And c.Value _
<= GetCFV(FC.Formula2, c) Then blnMatch = True: Exit For
Case xlNotBetween '2
If c.Value < GetCFV(FC.Formula1, c) Or c.Value _
GetCFV(FC.Formula2, c) Then blnMatch = True: Exit For
Case xlEqual '3
If c.Value = GetCFV(FC.Formula1, c) Then blnMatch = True: Exit For
Case xlNotEqual '4
If c.Value < GetCFV(FC.Formula1, c) Then blnMatch = True: Exit For
Case xlGreater '5
If c.Value GetCFV(FC.Formula1, c) Then blnMatch = True: Exit For
Case xlGreaterEqual '6
If c.Value = GetCFV(FC.Formula1, c) Then blnMatch = True: Exit For
Case xlLess '7
If c.Value < GetCFV(FC.Formula1, c) Then blnMatch = True: Exit For
Case xlLessEqual '8
If c.Value <= GetCFV(FC.Formula1, c) Then blnMatch = True: Exit For
End Select
Else
'Handle Type2-xlExternal (If 'Formula Is')
If Evaluate(Application.ConvertFormula( _
Application.ConvertFormula(FC.Formula1, xlA1, xlR1C1), _
xlR1C1, xlA1, , c)) Then blnMatch = True: Exit For
End If
Next

If blnMatch Then GetCFColorIndex = FC.Interior.ColorIndex
End Function
'-------------------------------------------------------------------------------
Function GetCFV(strData As Variant, c As Range)
'Get text string or numeric from CF formula
If IsNumeric(strData) Then
GetCFV = CDbl(strData)
ElseIf InStr(strData, Chr(34)) Then
GetCFV = Mid(strData, 3, Len(strData) - 3)
Else
GetCFV = Range(Mid(Application.ConvertFormula( _
Application.ConvertFormula(strData, xlA1, xlR1C1), _
xlR1C1, xlA1, , c), 2))
End If
End Function
'-------------------------------------------------------------------------------


--
Jacob


"Faraz A. Qureshi" wrote:

I have a code for selecting the cells with specific interior color be
selected as follows, however, how to add cells colored similarly but due to
conditional formatting, whether by the 1st, 2nd or any condition?

Sub SlctClrCel(CONTROL As IRibbonControl)
Dim CRange As Range
Dim A As Range
Dim B As Range
RETRY:
Set A = Application.InputBox("Select A Sample Cell With The Desired Interior
Color.", Type:=8)
Set B = Application.InputBox("Looking In Which Range?" & vbNewLine &
"Remember To Select Only The Necessary Cells", Type:=8)
For Each C In B
If C.Interior.ColorIndex = A.Interior.ColorIndex Then
If CRange Is Nothing Then
Set CRange = C
Else
Set CRange = Union(CRange, C)
End If
End If
Next
If Not CRange Is Nothing Then
CRange.Select
Else
MsgBox ("None Found!")
End If
End Sub

--
Thanx in advance,
Best Regards,

Faraz
Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 211
Default Colors of Cells With Conditional Formatting
Dear Jacob,

Problem still exists. Can't describe the same in words. Emailing you a
sample file on your yahoo's jacs address. Please c if u can help me!
Sure am thankful 4 all your help pal!
--
Thanx in advance,
Best Regards,

Faraz


"Jacob Skaria" wrote:

Paste both functions in a module and try with the macro.

The below will return the colorindex of cell D5. Try conditional formatting
cell D5 with fill color red and run the macro both with the condition and
without....If the cell is colored due to CF the macro will return the
colorindex applied...

Sub Macro1()
MsgBox GetCFColorIndex(Range("D5"))
End Sub


--
Jacob


"Faraz A. Qureshi" wrote:

Nice 2 hear from u after such a longtime pal!
Sure had been busy myself!
By the way Your recommended Function no doubt presents a good way but the
result changes to "1" everytime I carryout a step after inserting the UDF
GetCFColorIndex, any reason?

By the way, instead of a function any idea for a procedure?
--
Thanx in advance,
Best Regards,

Faraz


"Jacob Skaria" wrote:

Hi Faraz

Try the below function to get the color index of a conditional formatted cell.

Function GetCFColorIndex(c As Range) As Variant
Dim intCount As Integer, FC As FormatCondition, blnMatch As Boolean
If c.Count < 1 Then Exit Function
For intCount = 1 To c.FormatConditions.Count
'Loop through each Contidional Formatting
Set FC = c.FormatConditions(intCount)
Application.Volatile
If FC.Type = 1 Then
'Handle Type1-xlExpression (If 'Cell Value Is')
Select Case FC.Operator
Case xlBetween '1
If c.Value = GetCFV(FC.Formula1, c) And c.Value _
<= GetCFV(FC.Formula2, c) Then blnMatch = True: Exit For
Case xlNotBetween '2
If c.Value < GetCFV(FC.Formula1, c) Or c.Value _
GetCFV(FC.Formula2, c) Then blnMatch = True: Exit For
Case xlEqual '3
If c.Value = GetCFV(FC.Formula1, c) Then blnMatch = True: Exit For
Case xlNotEqual '4
If c.Value < GetCFV(FC.Formula1, c) Then blnMatch = True: Exit For
Case xlGreater '5
If c.Value GetCFV(FC.Formula1, c) Then blnMatch = True: Exit For
Case xlGreaterEqual '6
If c.Value = GetCFV(FC.Formula1, c) Then blnMatch = True: Exit For
Case xlLess '7
If c.Value < GetCFV(FC.Formula1, c) Then blnMatch = True: Exit For
Case xlLessEqual '8
If c.Value <= GetCFV(FC.Formula1, c) Then blnMatch = True: Exit For
End Select
Else
'Handle Type2-xlExternal (If 'Formula Is')
If Evaluate(Application.ConvertFormula( _
Application.ConvertFormula(FC.Formula1, xlA1, xlR1C1), _
xlR1C1, xlA1, , c)) Then blnMatch = True: Exit For
End If
Next

If blnMatch Then GetCFColorIndex = FC.Interior.ColorIndex
End Function
'-------------------------------------------------------------------------------
Function GetCFV(strData As Variant, c As Range)
'Get text string or numeric from CF formula
If IsNumeric(strData) Then
GetCFV = CDbl(strData)
ElseIf InStr(strData, Chr(34)) Then
GetCFV = Mid(strData, 3, Len(strData) - 3)
Else
GetCFV = Range(Mid(Application.ConvertFormula( _
Application.ConvertFormula(strData, xlA1, xlR1C1), _
xlR1C1, xlA1, , c), 2))
End If
End Function
'-------------------------------------------------------------------------------


--
Jacob


"Faraz A. Qureshi" wrote:

I have a code for selecting the cells with specific interior color be
selected as follows, however, how to add cells colored similarly but due to
conditional formatting, whether by the 1st, 2nd or any condition?

Sub SlctClrCel(CONTROL As IRibbonControl)
Dim CRange As Range
Dim A As Range
Dim B As Range
RETRY:
Set A = Application.InputBox("Select A Sample Cell With The Desired Interior
Color.", Type:=8)
Set B = Application.InputBox("Looking In Which Range?" & vbNewLine &
"Remember To Select Only The Necessary Cells", Type:=8)
For Each C In B
If C.Interior.ColorIndex = A.Interior.ColorIndex Then
If CRange Is Nothing Then
Set CRange = C
Else
Set CRange = Union(CRange, C)
End If
End If
Next
If Not CRange Is Nothing Then
CRange.Select
Else
MsgBox ("None Found!")
End If
End Sub

--
Thanx in advance,
Best Regards,

Faraz
Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 8,520
Default Colors of Cells With Conditional Formatting
Faraz; I should have mentioned the code works fine only in XL2003.
Conditional formatting has changed a lot in XL2007 and I havent tested this
for XL2007.

--
Jacob


"Faraz A. Qureshi" wrote:

Dear Jacob,

Problem still exists. Can't describe the same in words. Emailing you a
sample file on your yahoo's jacs address. Please c if u can help me!
Sure am thankful 4 all your help pal!
--
Thanx in advance,
Best Regards,

Faraz


"Jacob Skaria" wrote:

Paste both functions in a module and try with the macro.

The below will return the colorindex of cell D5. Try conditional formatting
cell D5 with fill color red and run the macro both with the condition and
without....If the cell is colored due to CF the macro will return the
colorindex applied...

Sub Macro1()
MsgBox GetCFColorIndex(Range("D5"))
End Sub


--
Jacob


"Faraz A. Qureshi" wrote:

Nice 2 hear from u after such a longtime pal!
Sure had been busy myself!
By the way Your recommended Function no doubt presents a good way but the
result changes to "1" everytime I carryout a step after inserting the UDF
GetCFColorIndex, any reason?

By the way, instead of a function any idea for a procedure?
--
Thanx in advance,
Best Regards,

Faraz


"Jacob Skaria" wrote:

Hi Faraz

Try the below function to get the color index of a conditional formatted cell.

Function GetCFColorIndex(c As Range) As Variant
Dim intCount As Integer, FC As FormatCondition, blnMatch As Boolean
If c.Count < 1 Then Exit Function
For intCount = 1 To c.FormatConditions.Count
'Loop through each Contidional Formatting
Set FC = c.FormatConditions(intCount)
Application.Volatile
If FC.Type = 1 Then
'Handle Type1-xlExpression (If 'Cell Value Is')
Select Case FC.Operator
Case xlBetween '1
If c.Value = GetCFV(FC.Formula1, c) And c.Value _
<= GetCFV(FC.Formula2, c) Then blnMatch = True: Exit For
Case xlNotBetween '2
If c.Value < GetCFV(FC.Formula1, c) Or c.Value _
GetCFV(FC.Formula2, c) Then blnMatch = True: Exit For
Case xlEqual '3
If c.Value = GetCFV(FC.Formula1, c) Then blnMatch = True: Exit For
Case xlNotEqual '4
If c.Value < GetCFV(FC.Formula1, c) Then blnMatch = True: Exit For
Case xlGreater '5
If c.Value GetCFV(FC.Formula1, c) Then blnMatch = True: Exit For
Case xlGreaterEqual '6
If c.Value = GetCFV(FC.Formula1, c) Then blnMatch = True: Exit For
Case xlLess '7
If c.Value < GetCFV(FC.Formula1, c) Then blnMatch = True: Exit For
Case xlLessEqual '8
If c.Value <= GetCFV(FC.Formula1, c) Then blnMatch = True: Exit For
End Select
Else
'Handle Type2-xlExternal (If 'Formula Is')
If Evaluate(Application.ConvertFormula( _
Application.ConvertFormula(FC.Formula1, xlA1, xlR1C1), _
xlR1C1, xlA1, , c)) Then blnMatch = True: Exit For
End If
Next

If blnMatch Then GetCFColorIndex = FC.Interior.ColorIndex
End Function
'-------------------------------------------------------------------------------
Function GetCFV(strData As Variant, c As Range)
'Get text string or numeric from CF formula
If IsNumeric(strData) Then
GetCFV = CDbl(strData)
ElseIf InStr(strData, Chr(34)) Then
GetCFV = Mid(strData, 3, Len(strData) - 3)
Else
GetCFV = Range(Mid(Application.ConvertFormula( _
Application.ConvertFormula(strData, xlA1, xlR1C1), _
xlR1C1, xlA1, , c), 2))
End If
End Function
'-------------------------------------------------------------------------------


--
Jacob


"Faraz A. Qureshi" wrote:

I have a code for selecting the cells with specific interior color be
selected as follows, however, how to add cells colored similarly but due to
conditional formatting, whether by the 1st, 2nd or any condition?

Sub SlctClrCel(CONTROL As IRibbonControl)
Dim CRange As Range
Dim A As Range
Dim B As Range
RETRY:
Set A = Application.InputBox("Select A Sample Cell With The Desired Interior
Color.", Type:=8)
Set B = Application.InputBox("Looking In Which Range?" & vbNewLine &
"Remember To Select Only The Necessary Cells", Type:=8)
For Each C In B
If C.Interior.ColorIndex = A.Interior.ColorIndex Then
If CRange Is Nothing Then
Set CRange = C
Else
Set CRange = Union(CRange, C)
End If
End If
Next
If Not CRange Is Nothing Then
CRange.Select
Else
MsgBox ("None Found!")
End If
End Sub

--
Thanx in advance,
Best Regards,

Faraz
Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Counting Colors of Cells set by Conditional Formatting RobFJ[_3_] Excel Worksheet Functions 2 February 16th 10 01:01 PM
Conditional Formatting Based on Colors in a Horz Range of Cells DOUG ECKERT[_2_] Excel Discussion (Misc queries) 10 December 19th 08 02:29 PM
Conditional Formatting - Not all colors work? PSS New Users to Excel 1 April 18th 08 11:49 PM
Conditional Formatting - more than 4 cell colors sharakbh Excel Worksheet Functions 5 March 1st 08 12:09 AM
Conditional Formatting in 6 colors Sterling Excel Discussion (Misc queries) 1 October 6th 06 11:22 PM


All times are GMT +1. The time now is 01:29 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement