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

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

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

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