Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA adding Conditional Format to Range referencing another column value
I have the following ranges setup to give a number of rows in a column. The
first Cond. Format sets the Font colour to white if the cell value is zero, this is successful. What I require to do is set a second Cond. Format in the same range but referencing a value in the same row but in another column. What do I need to use as the Formula1 for the 2nd Format. Regards Set rng = appExcel.ActiveCell.Offset(rowOffset:=0, columnOffset:=2) Set rngStart22 = Range(rng, rng.Offset(intSummaryLineCount - 1, 0)) With rngStart22 .FormatConditions.Delete .FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="0" With .FormatConditions(1).Font .ThemeColor = xlThemeColorDark1 .TintAndShade = 0 End With .FormatConditions(1).StopIfTrue = True .FormatConditions.Add Type:=xlExpression, Formula1:= ?????????? = "1" With Selection.FormatConditions(2).Font .Color = -16727809 .TintAndShade = 0 End With Selection.FormatConditions(2).StopIfTrue = True End With |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA adding Conditional Format to Range referencing another column
Sub Macro1()
Dim sl As Range Set sl = Selection With sl .FormatConditions.Delete .FormatConditions.Add Type:=xlExpression, Formula1:="=RC" & ..Columns(2).Column & "=3" .FormatConditions(1).Interior.ColorIndex = 27 End With End Sub with conditional formatting, you "lock" the column by precedign the column letter with a "$" . Here, we want the second column of the selection, so ..columns(2) the actual column number is columns(2).Column, which is what we need in the formula "Terry" wrote: I have the following ranges setup to give a number of rows in a column. The first Cond. Format sets the Font colour to white if the cell value is zero, this is successful. What I require to do is set a second Cond. Format in the same range but referencing a value in the same row but in another column. What do I need to use as the Formula1 for the 2nd Format. Regards Set rng = appExcel.ActiveCell.Offset(rowOffset:=0, columnOffset:=2) Set rngStart22 = Range(rng, rng.Offset(intSummaryLineCount - 1, 0)) With rngStart22 .FormatConditions.Delete .FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="0" With .FormatConditions(1).Font .ThemeColor = xlThemeColorDark1 .TintAndShade = 0 End With .FormatConditions(1).StopIfTrue = True .FormatConditions.Add Type:=xlExpression, Formula1:= ?????????? = "1" With Selection.FormatConditions(2).Font .Color = -16727809 .TintAndShade = 0 End With Selection.FormatConditions(2).StopIfTrue = True End With |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA adding Conditional Format to Range referencing another column
Thanks Patrick,
Could you please explain the number in brackets again (2), is this the number of columns offset from the range? The Range is C13 to C36, the value to be checked is in I13 to I36. I also note the ColorIndex value, is there a way to find these values? Regards "Patrick Molloy" wrote in message ... Sub Macro1() Dim sl As Range Set sl = Selection With sl .FormatConditions.Delete .FormatConditions.Add Type:=xlExpression, Formula1:="=RC" & .Columns(2).Column & "=3" .FormatConditions(1).Interior.ColorIndex = 27 End With End Sub with conditional formatting, you "lock" the column by precedign the column letter with a "$" . Here, we want the second column of the selection, so .columns(2) the actual column number is columns(2).Column, which is what we need in the formula "Terry" wrote: I have the following ranges setup to give a number of rows in a column. The first Cond. Format sets the Font colour to white if the cell value is zero, this is successful. What I require to do is set a second Cond. Format in the same range but referencing a value in the same row but in another column. What do I need to use as the Formula1 for the 2nd Format. Regards Set rng = appExcel.ActiveCell.Offset(rowOffset:=0, columnOffset:=2) Set rngStart22 = Range(rng, rng.Offset(intSummaryLineCount - 1, 0)) With rngStart22 .FormatConditions.Delete .FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="0" With .FormatConditions(1).Font .ThemeColor = xlThemeColorDark1 .TintAndShade = 0 End With .FormatConditions(1).StopIfTrue = True .FormatConditions.Add Type:=xlExpression, Formula1:= ?????????? = "1" With Selection.FormatConditions(2).Font .Color = -16727809 .TintAndShade = 0 End With Selection.FormatConditions(2).StopIfTrue = True End With |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA adding Conditional Format to Range referencing another column
Hi Patrick,
I have used this solution to reference the column to be tested: ..FormatConditions.Add Type:=xlExpression, Formula1:="=R[0]C[6]=1" Thanks for your help, Regards "Terry" wrote in message ... Thanks Patrick, Could you please explain the number in brackets again (2), is this the number of columns offset from the range? The Range is C13 to C36, the value to be checked is in I13 to I36. I also note the ColorIndex value, is there a way to find these values? Regards "Patrick Molloy" wrote in message ... Sub Macro1() Dim sl As Range Set sl = Selection With sl .FormatConditions.Delete .FormatConditions.Add Type:=xlExpression, Formula1:="=RC" & .Columns(2).Column & "=3" .FormatConditions(1).Interior.ColorIndex = 27 End With End Sub with conditional formatting, you "lock" the column by precedign the column letter with a "$" . Here, we want the second column of the selection, so .columns(2) the actual column number is columns(2).Column, which is what we need in the formula "Terry" wrote: I have the following ranges setup to give a number of rows in a column. The first Cond. Format sets the Font colour to white if the cell value is zero, this is successful. What I require to do is set a second Cond. Format in the same range but referencing a value in the same row but in another column. What do I need to use as the Formula1 for the 2nd Format. Regards Set rng = appExcel.ActiveCell.Offset(rowOffset:=0, columnOffset:=2) Set rngStart22 = Range(rng, rng.Offset(intSummaryLineCount - 1, 0)) With rngStart22 .FormatConditions.Delete .FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="0" With .FormatConditions(1).Font .ThemeColor = xlThemeColorDark1 .TintAndShade = 0 End With .FormatConditions(1).StopIfTrue = True .FormatConditions.Add Type:=xlExpression, Formula1:= ?????????? = "1" With Selection.FormatConditions(2).Font .Color = -16727809 .TintAndShade = 0 End With Selection.FormatConditions(2).StopIfTrue = True End With |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
conditional formatting referencing 3 cells by row in a range | Excel Discussion (Misc queries) | |||
In Excel, conditional format (font red) referencing another cell? | Excel Worksheet Functions | |||
display a conditional format in a range from another column | Excel Discussion (Misc queries) | |||
How do I conditional format by referencing failure/success? | Excel Worksheet Functions | |||
Referencing a Column in a Selected Range of Columns | Excel Programming |