Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2007 conditional formatting - how to get cell color?
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).Interior.Color End Function Regardless of the cell's color, the returned value is always 16777215; if I use the ColorIndex property, the returned value is (-4142). Do I need to decode these to RGB 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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2007 conditional formatting - how to get cell color?
Hi,
You are looking at cell colors you are not looking a conditional formatting colors: With Selection.FormatConditions(1).ColorScaleCriteria(1 ).FormatColor .Color = 7039480 .TintAndShade = 0 End With However, you may be able to retrieve the info on what the conditional format is set for all the cells is but I don't believe you can retrieve what the current cells color is. For one thing you can apply multiple conditional formats to the same cell at the same time. For example it is possible in 2007 to format the cell with a Data Bar and a Color Scale and an Icon Set at the same time. And have them all display at the same time. Similarly with other combinations. -- Thanks, Shane Devenshire " wrote: 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).Interior.Color End Function Regardless of the cell's color, the returned value is always 16777215; if I use the ColorIndex property, the returned value is (-4142). Do I need to decode these to RGB 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2007 conditional formatting - how to get cell color?
To return the format applied to the cell by Conditional Formatting, you need to first determine if
the format condition is met. Try the code below HTH, Bernie MS Excel MVP Sub TestCFColorMacro() MsgBox CFColor(ActiveCell) End Sub '--------------------------------------------------------------------- Public Function CFColor(rng As Range) As Long '--------------------------------------------------------------------- Dim oFC As FormatCondition Dim sF1 As String Dim iRow As Long Dim iColumn As Long Dim IsCFMet As Boolean Set rng = rng(1, 1) If rng.FormatConditions.Count 0 Then For Each oFC In rng.FormatConditions If oFC.Type = xlCellValue Then IsCFMet = False Select Case oFC.Operator Case xlEqual IsCFMet = rng.Value = oFC.Formula1 Case xlNotEqual IsCFMet = rng.Value < oFC.Formula1 Case xlGreater IsCFMet = rng.Value oFC.Formula1 Case xlGreaterEqual IsCFMet = rng.Value = oFC.Formula1 Case xlLess IsCFMet = rng.Value < oFC.Formula1 Case xlLessEqual IsCFMet = rng.Value <= oFC.Formula1 IsCFMet = (rng.Value = oFC.Formula1 And _ rng.Value <= oFC.Formula2) Case xlNotBetween IsCFMet = (rng.Value < oFC.Formula1 Or _ rng.Value oFC.Formula2) End Select If IsCFMet Then CFColor = oFC.Interior.ColorIndex Exit Function End If Else 're-adjust the formula back to the formula that applies 'to the cell as relative formulae adjust to the activecell With Application iRow = rng.Row iColumn = rng.Column sF1 = .Substitute(oFC.Formula1, "ROW()", iRow) sF1 = .Substitute(sF1, "COLUMN()", iColumn) sF1 = .ConvertFormula(sF1, xlA1, xlR1C1) sF1 = .ConvertFormula(sF1, xlR1C1, xlA1, , rng) End With IsCFMet = rng.Parent.Evaluate(sF1) End If If IsCFMet Then Exit Function Next oFC End If 'rng.FormatConditions.Count 0 End Function wrote in message ... 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).Interior.Color End Function Regardless of the cell's color, the returned value is always 16777215; if I use the ColorIndex property, the returned value is (-4142). Do I need to decode these to RGB 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2007 conditional formatting - how to get cell color?
And I forgot to include that the code was based on Bob Phillips' function IsCFMet - I just
high-jacked it for my CFColor function. HTH, Bernie MS Excel MVP '--------------------------------------------------------------------- Public Function CFColor(rng As Range) As Long '--------------------------------------------------------------------- Dim oFC As FormatCondition Dim sF1 As String Dim iRow As Long Dim iColumn As Long Dim IsCFMet As Boolean Set rng = rng(1, 1) If rng.FormatConditions.Count 0 Then For Each oFC In rng.FormatConditions If oFC.Type = xlCellValue Then IsCFMet = False Select Case oFC.Operator Case xlEqual IsCFMet = rng.Value = oFC.Formula1 Case xlNotEqual IsCFMet = rng.Value < oFC.Formula1 Case xlGreater IsCFMet = rng.Value oFC.Formula1 Case xlGreaterEqual IsCFMet = rng.Value = oFC.Formula1 Case xlLess IsCFMet = rng.Value < oFC.Formula1 Case xlLessEqual IsCFMet = rng.Value <= oFC.Formula1 IsCFMet = (rng.Value = oFC.Formula1 And _ rng.Value <= oFC.Formula2) Case xlNotBetween IsCFMet = (rng.Value < oFC.Formula1 Or _ rng.Value oFC.Formula2) End Select If IsCFMet Then CFColor = oFC.Interior.ColorIndex Exit Function End If Else 're-adjust the formula back to the formula that applies 'to the cell as relative formulae adjust to the activecell With Application iRow = rng.Row iColumn = rng.Column sF1 = .Substitute(oFC.Formula1, "ROW()", iRow) sF1 = .Substitute(sF1, "COLUMN()", iColumn) sF1 = .ConvertFormula(sF1, xlA1, xlR1C1) sF1 = .ConvertFormula(sF1, xlR1C1, xlA1, , rng) End With IsCFMet = rng.Parent.Evaluate(sF1) End If If IsCFMet Then Exit Function Next oFC End If 'rng.FormatConditions.Count 0 End Function wrote in message ... 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).Interior.Color End Function Regardless of the cell's color, the returned value is always 16777215; if I use the ColorIndex property, the returned value is (-4142). Do I need to decode these to RGB 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2007: cell shading (not conditional formatting) | Excel Discussion (Misc queries) | |||
Conditional Formatting (Excel 2007) Format Row Color Based on X | Excel Discussion (Misc queries) | |||
Excel 2007 conditional formatting & cell colour. | Excel Worksheet Functions | |||
Apply conditional formatting to individual cell Excel 2007 | Excel Worksheet Functions | |||
Conditional Formatting and Cell Color | Excel Worksheet Functions |