Microsoft Excel - conditional formatting - multiple conditions not reflected properly in the VBA Objects
Asked By Jim Brown
16-Oct-09 03:04 PM
I have a conditional format where if the cell contains 'y' the cell background is green if the cell contains 'z' the cell background is orange. This is working correctly. I want to pull this conditional formatting into text using VBA, but it appears that the objects within VBA are not correct.
In the immediate window, typed these commands and got these results:
print activecell.formatconditions(1).formula1 & ":" & activecell.formatconditions(1).interior.colorindex
=UPPER($A671)="Y":44
print activecell.formatconditions(2).formula1 & ":" & activecell.formatconditions(2).interior.colorindex
=UPPER($A671)="Y":4
The colorindex values are correct. Green is 4 and Orange is 44, but the formulas are not. The first one should print as =UPPER($A671)="Z":44
What am I doing wrong?
conditional formatting - multiple conditions not reflected properly in the VBA Objectsconditional formatting - multiple conditions not reflected properly in the VBA Objects
More information -
This is happening in Excel 2007. About Microsoft Excel reports this version:
Microsoft Office Excel 2007 (12.0.4518.1014) MSO (12.0.6425.1000)
I have MS Office 2003 installed on my PC. Only Excel is from 2007
Jonathan VH replied to Jim Brown
If you open Manage Rules under Conditional Formatting, do you only see two rules? I'm wondering if perhaps you actually have more than two and the first two both have the "Y" formula and the third is actually the "Z" formula.
I don't have this issue with my Excel 2007 (12.0.6504.5001) SP2. It would have been a problem when I wrote this two weeks ago:
http://www.eggheadcafe.com/community/aspnet/66/10121012/re-ok-lets-try-something.aspx
conditional formatting - multiple conditions not reflected properly in the VBA Objects
Yes, I see exactly two rules, populated as expected.
conditional formatting - multiple conditions not reflected properly in the VBA Objects
Also, inside the VBA environment, it tells me that there are two conditions
Jonathan VH replied to Jim Brown
So you ran something like this and get the same results?
Sub Formats()
Dim fmt As FormatCondition
For Each fmt In ActiveCell.FormatConditions
Debug.Print fmt.Formula1 & ":" & fmt.Interior.ColorIndex
Next
End Sub
As my Excel doesn't behave like this, it sounds like you need to update yours.
Jim Brown replied to Jonathan VH
Yes, I was running code to pull the formats and write them out to adjacent cells on the same worksheet. It's an effective way to document what I've done in the past, so that I don't need to reinvent the wheel. I noticed this problem when my output did not have the second format. This code worked perfectly in Excel2003, but Excel2007 seems to have a problem.
Jonathan VH replied to Jim Brown
conditional formatting
I just need to convince enterprise desktop support to do this. If it were me, it would already be done.
Counting cells with conditional formatting Excel I am trying to count the cells in a range that have a certain fill color in Excel 2003. Is there a way to use COUNTIF? If so, how do I write the looking for? Or, is there another formula? Many thanks to whomever can answer this one! Excel Worksheet Discussions Microsoft Excel (1) FormatCondition (1) Excel 2003 (1) Intersect (1) Interior (1) Workbook (1) COUNTIF (1) VBA (1) Easiest to Count extra code. See Chip Pearson's site for code and instructions. http: / / www.cpearson.com / excel / CFColors.htm Gord Dibben MS Excel MVP in = 20 criteria = 20 to = 20 Use the
Excel 2007 conditional formatting - how to get cell color? Excel I have a range of cells to which I've applied a simple red-to-green color scale using the Conditional Formatting menu in Excel 2007. I want to read the resulting colors that Excel has applied to the cell interiors from VBA to use these colors for an associated graph. As a test, I wrote the following worksheet function to see what colors Excel had assigned: Function CellColor(c As Range) As Variant Application.Volatile CellColor = c(1, 1 values? If so, how? I don't need to solve this for prior versions of Excel as I realize the color handling was much different. Thanks for your help. Bob Excel Programming Discussions ColorScaleCriteria (1) FormatConditions (1) Microsoft Excel (1) FormatColor (1) Excel 2007 (1) Interior (1) TestCFColorMacro (1) Worksheet (1) Hi, You
Export Conditional Formatting Rules Excel 2007 Excel Is it possible to export conditional formatting rules, so I can apply them to other spreadsheets instead of typing them all over again? Julian Excel Discussions FormatCondition (1) CustomView (1) AutoFilter (1) Excel 2007 (1) Worksheets (1) Sheets (1) Error (1) Workbook (1 microsoft.public.excel : 'Paste Special - Formatting' does not work in Excel 2007, then? - - Paul Hyett, Cheltenham (change 'invalid83261' to 'blueyonder' to email me) I thought it
Conditional Formatting from VBA Excel I'm trying to set the Conditional formatting of a cell in Excel from Access - Don't ask ! The condition? I the characters 'KC' appear anywhere in C11 fine but Condition does not work and when I examine the Conditional formatting expression in Excel the 'C11' in the expression has been changed to 'IU17' every time ?? Can anyone please help? Excel Programming Discussions FormatConditions (1) Microsoft Excel (1) Interior (1) Excel (1) ActiveCell.Address (1) ISNUMBER (1) VBA (1) FormatConditions.Delete (1) Dim objSht Set objSht Formula1: = " = ISNUMBER(SEARCH(""KC"", C11))" .Interior.ColorIndex = 43 End With End With Gord Dibben MS Excel MVP I think that will do the same Gord. Dim objSht Set objSht = ActiveSheet With
Bedingte Formatierung wird nicht transponiert Excel Hallo, nach Bearbeiten-Inhalte einf??gen-transponieren werden meine zahlreichen bedingten Formatierungen nicht mittransponiert. Ich arbeite noch mit Excel2002 Was kann man tun? Gru?? Andreas Dusin Excel - German Discussions VBScript.RegExp (1) Microsoft Excel (1) Application.CutCopyMode (1) Selection.Areas.Count (1) Application.ReferenceStyle (1) ActiveCell.Column (1) Selection mitgedreht werden k??nnen oder korrekt ber??cksichtigt werden. - - Moin+Gruss Alexander - MVP for MS Excel - www.xxcl.de - mso2000sp3 - -7-2 Hallo Alexander, herzlichen Dank f??r die nachvollziehbare Info abgefragt. Vielen Dank im Voraus. Peter Sub TransposeWithConditionalFormatting() Dim old_ref_style As Variant Dim fc1 As FormatCondition Dim fc2 As FormatCondition Dim quelle As Range Dim ziel As Range Dim f1 As String Dim f2 As