Hi folks
While doing Conditional Formatting via VBA, if we assign String array to Formula1 then CF is not taking place..
instead if we hardcode the same, its working..
Following is the code
Dim rangeArray() As Double
Dim TheRange As Range
Dim rangeFormulaArray() As String
Set TheRange = Range(Cells(vFRow + i * vRowPerModel + 1, vFCol + vFCStart), Cells(vFRow + i * vRowPerModel + 1, vFCol + vColCount - 1))
ReDim rangeFormulaArray(vFRow + i * vRowPerModel + 1 To vFRow + i * vRowPerModel + 1, vFCol + vFCStart To vFCol + vColCount - 1)
For j = vFCol + vFCStart To vFCol + vColCount - 1
rangeFormulaArray(vFRow + i * vRowPerModel + 1, j) = "=" & Cells(vFRow + i * vRowPerModel + 1, j).Address & "<> RoundDown(" + Cells(vFRow + i * vRowPerModel + 1, j).Address & " / " & vMCQ & ", 0) * " & vMCQ Next j
TheRange.FormatConditions.Delete
TheRange.FormatConditions.Add Type:=xlExpression, Operator:=xlNotEqual, Formula1:=rangeFormulaArray
TheRange.FormatConditions(1).Font.Bold = True
Next j
Please guide.
Any help will be cordially appreciated.
Regards
Gajendra
While doing Conditional Formatting via VBA, if we assign String array to Formula1 then CF is not taking place..
instead if we hardcode the same, its working..
Following is the code
Dim rangeArray() As Double
Dim TheRange As Range
Dim rangeFormulaArray() As String
Set TheRange = Range(Cells(vFRow + i * vRowPerModel + 1, vFCol + vFCStart), Cells(vFRow + i * vRowPerModel + 1, vFCol + vColCount - 1))
ReDim rangeFormulaArray(vFRow + i * vRowPerModel + 1 To vFRow + i * vRowPerModel + 1, vFCol + vFCStart To vFCol + vColCount - 1)
For j = vFCol + vFCStart To vFCol + vColCount - 1
rangeFormulaArray(vFRow + i * vRowPerModel + 1, j) = "=" & Cells(vFRow + i * vRowPerModel + 1, j).Address & "<> RoundDown(" + Cells(vFRow + i * vRowPerModel + 1, j).Address & " / " & vMCQ & ", 0) * " & vMCQ Next j
TheRange.FormatConditions.Delete
TheRange.FormatConditions.Add Type:=xlExpression, Operator:=xlNotEqual, Formula1:=rangeFormulaArray
TheRange.FormatConditions(1).Font.Bold = True
Next j
Please guide.
Any help will be cordially appreciated.
Regards
Gajendra