Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Determining What FormatCondition Has Been Chosen
I have formatted a cell with 3 conditions. What Excel Visual Basic elements
will let me know which of the 3, if any, conditions were used to format the cell. Bradc |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Determining What FormatCondition Has Been Chosen
There is no such method in VBA which allows you to do this. The onl things I found about conditional format is that you can get th conditional formats of a cell by using the conditional formattin collection eg. Sheet1.Range("A1").FormatConditions, But not whic format was used itself. You might wanna see which conditional format is activated by listin out the conditions in the excel spreadsheet itself, seeing whic condition gives true/false values. Or give each value a differen format. However, since you want to see it in VBA.. I do not believe yo can. Thats the only way I think you can debug it -- KellTaine ----------------------------------------------------------------------- KellTainer's Profile: http://www.excelforum.com/member.php...fo&userid=3432 View this thread: http://www.excelforum.com/showthread.php?threadid=55074 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Determining What FormatCondition Has Been Chosen
See http://www.xldynamic.com/source/xld.CFConditions.html
-- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "John/Churchwell" wrote in message ... I have formatted a cell with 3 conditions. What Excel Visual Basic elements will let me know which of the 3, if any, conditions were used to format the cell. Bradc |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Determining What FormatCondition Has Been Chosen
Hi John
Take a closer look at these 2 macros ore look at Chip Pearson WWW Sub WhichMacroToRun() 'Leo Heuser, 8-6-2006 '// Modifyed by Joergen Bondesen, 8-6-2006 Dim Cell As Range Dim CheckRange As Range Dim FCNumber As Long Set CheckRange = ActiveSheet.Cells. _ SpecialCells(xlCellTypeAllFormatConditions) For Each Cell In CheckRange.Cells FCNumber = ActiveCondition(Cell) ' Caling Chips funktion '**** If FCNumber 0 Then Select Case Cell.FormatConditions(FCNumber).Interior _ .ColorIndex Case Is = 3 'Red 'Run Macro1 Case Is = 50 'Green 'Run Macro2 Case Else End Select End If Next Cell End Sub ' <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< <<<<<<<< Option Explicit '---------------------------------------------------------- ' Procedure : ConditionalsFormattingConvert ' Date : 20060607 ' Author : Joergen Bondesen ' Modifyed by : ' Purpose : Finding all cells with displayed ' Conditionals Formatting Red and Green ' Note : Macro will place counter offset(0,1) '---------------------------------------------------------- ' Sub ConditionalsFormattingConvert() Dim setASName As Worksheet Dim ASName As String Dim CopyName As Worksheet Dim SpCellsConFor As Range Dim cell As Range Dim count3 As Long Dim count50 As Long '// Sheets Name Set setASName = ActiveSheet ASName = setASName.Name '// Copy Sheet setASName.Copy setASName Set CopyName = ActiveSheet '// Name CopySheet ActiveSheet.Name = "CopySheet" '// Conditionals Formatting Cells Set SpCellsConFor = ActiveCell.SpecialCells _ (xlCellTypeAllFormatConditions) '// SpCellsConFor.Select '// Sub PasteFC '// Find Red and green cells For Each cell In SpCellsConFor If cell.Interior.ColorIndex = 3 Then '// Replase with your macro setASName.Range(cell.Address).Offset(0, 1).Value = _ 2 + count3 count3 = count3 + 1 ElseIf cell.Interior.ColorIndex = 50 Then '// Replase with your macro setASName.Range(cell.Address).Offset(0, 1).Value = _ 22 + count50 count50 = count50 + 1 End If Next cell '// Delete CopySheet Application.DisplayAlerts = False CopyName.Delete Application.DisplayAlerts = True ActiveCell.Select End Sub 'Conditional format convert (remove) ' if condition is fulfilled Private Sub PasteFC() Application.ScreenUpdating = False Dim rWhole As Range Dim rCell As Range Dim ndx As Integer Dim FCFont As Font Dim FCBorder As Border Dim FCInt As Interior Dim x As Integer Dim iBorders(3) As Integer iBorders(0) = xlLeft iBorders(1) = xlRight iBorders(2) = xlTop iBorders(3) = xlBottom Set rWhole = Selection For Each rCell In rWhole rCell.Select ndx = ActiveCondition(rCell) If ndx < 0 Then 'Change the Font info Set FCFont = rCell.FormatConditions(ndx).Font With rCell.Font .Bold = NewFC(.Bold, FCFont.Bold) .Italic = NewFC(.Italic, FCFont.Italic) .Underline = NewFC(.Underline, FCFont.Underline) .Strikethrough = NewFC(.Strikethrough, FCFont.Strikethrough) .ColorIndex = NewFC(.ColorIndex, FCFont.ColorIndex) End With 'Change the Border Info for each of the 4 types For x = 0 To 3 Set FCBorder = rCell.FormatConditions(ndx).Borders(iBorders(x)) With rCell.Borders(iBorders(x)) .LineStyle = NewFC(.LineStyle, FCBorder.LineStyle) .Weight = NewFC(.Weight, FCBorder.Weight) .ColorIndex = NewFC(.ColorIndex, FCBorder.ColorIndex) '*** End With Next x 'Change the interior info Set FCInt = rCell.FormatConditions(ndx).Interior With rCell.Interior .ColorIndex = NewFC(.ColorIndex, FCInt.ColorIndex) .Pattern = NewFC(.Pattern, FCInt.Pattern) End With 'Delete FC rCell.FormatConditions.Delete End If Next rWhole.Select Application.ScreenUpdating = True MsgBox ("The Formatting based on the Conditions" & vbCrLf & _ "in the range " & rWhole.Address & vbCrLf & _ "has been made standard for those cells" & vbCrLf & _ "and the Conditional Formatting has been removed") End Sub Function NewFC(vCurrent As Variant, vNew As Variant) If IsNull(vNew) Then NewFC = vCurrent Else NewFC = vNew End If End Function Function ActiveCondition(rng As Range) As Integer 'Chip Pearson http://www.cpearson.com/excel/CFColors.htm Dim ndx As Long Dim FC As FormatCondition 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 If CDbl(rng.Value) = CDbl(FC.Formula1) And _ CDbl(rng.Value) <= CDbl(FC.Formula2) Then ActiveCondition = ndx Exit Function End If Case xlGreater If CDbl(rng.Value) CDbl(FC.Formula1) Then ActiveCondition = ndx Exit Function End If Case xlEqual If CDbl(rng.Value) = CDbl(FC.Formula1) Then ActiveCondition = ndx Exit Function End If Case xlGreaterEqual If CDbl(rng.Value) = CDbl(FC.Formula1) Then ActiveCondition = ndx Exit Function End If Case xlLess If CDbl(rng.Value) < CDbl(FC.Formula1) Then ActiveCondition = ndx Exit Function End If Case xlLessEqual If CDbl(rng.Value) <= CDbl(FC.Formula1) Then ActiveCondition = ndx Exit Function End If Case xlNotEqual If CDbl(rng.Value) < CDbl(FC.Formula1) Then ActiveCondition = ndx Exit Function End If Case xlNotBetween If CDbl(rng.Value) <= CDbl(FC.Formula1) Or _ CDbl(rng.Value) = CDbl(FC.Formula2) Then ActiveCondition = ndx Exit Function End If Case Else Debug.Print "UNKNOWN OPERATOR" 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 -- Best Regards Joergen Bondesen "John/Churchwell" wrote in message ... I have formatted a cell with 3 conditions. What Excel Visual Basic elements will let me know which of the 3, if any, conditions were used to format the cell. Bradc |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Determining What FormatCondition Has Been Chosen
The real reason for asking is that the conditions set the
interior.colorindex. When I check the cell to see what interior.colorindex is used, it always returns -4142 (likely xlAutomatic). This seems like an error to me. Since I can't count on the colorindex to tell what happened, I tried to see if I could find out which condition hit, then check its colorindex. Is there another attribute that tells what color is displayed instead of what the interior.colorindex has as a value. All in all, it seems like an error in Excel / VBA. Brad C. On 6/10/06 11:25 PM, in article , "KellTainer" wrote: There is no such method in VBA which allows you to do this. The only things I found about conditional format is that you can get the conditional formats of a cell by using the conditional formatting collection eg. Sheet1.Range("A1").FormatConditions, But not which format was used itself. You might wanna see which conditional format is activated by listing out the conditions in the excel spreadsheet itself, seeing which condition gives true/false values. Or give each value a different format. However, since you want to see it in VBA.. I do not believe you can. Thats the only way I think you can debug it. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Determining What FormatCondition Has Been Chosen
That is because conditional formatting doesn't set the interior property, it
is somewhere other than the cell. The only way to know is to apply the same tests as CF, or test the actual conditions as I show in that paper. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Brad Churchwell" wrote in message ... The real reason for asking is that the conditions set the interior.colorindex. When I check the cell to see what interior.colorindex is used, it always returns -4142 (likely xlAutomatic). This seems like an error to me. Since I can't count on the colorindex to tell what happened, I tried to see if I could find out which condition hit, then check its colorindex. Is there another attribute that tells what color is displayed instead of what the interior.colorindex has as a value. All in all, it seems like an error in Excel / VBA. Brad C. On 6/10/06 11:25 PM, in article , "KellTainer" wrote: There is no such method in VBA which allows you to do this. The only things I found about conditional format is that you can get the conditional formats of a cell by using the conditional formatting collection eg. Sheet1.Range("A1").FormatConditions, But not which format was used itself. You might wanna see which conditional format is activated by listing out the conditions in the excel spreadsheet itself, seeing which condition gives true/false values. Or give each value a different format. However, since you want to see it in VBA.. I do not believe you can. Thats the only way I think you can debug it. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Determining What FormatCondition Has Been Chosen
Bob,
When I copy the function CFColorCount into my workbook, the following line displays a compile error CFColorCount = CFColorCount - _ CLng(CFColorindex(cell, text) ) = ciValue ) What am I missing? I have the CFColorindex function there already. "Bob Phillips" wrote: See http://www.xldynamic.com/source/xld.CFConditions.html -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "John/Churchwell" wrote in message ... I have formatted a cell with 3 conditions. What Excel Visual Basic elements will let me know which of the 3, if any, conditions were used to format the cell. Bradc |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Go to a chosen worksheet | Excel Worksheet Functions | |||
Gridlines have to be chosen each time | Excel Discussion (Misc queries) | |||
Font different than one chosen | Excel Discussion (Misc queries) | |||
put a result into a chosen cell | Excel Discussion (Misc queries) | |||
How do I let only one box be chosen when there are several? | Excel Worksheet Functions |