Conditional Formatting via VBA

[gaj_cod]

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

 

[gaj_cod]

Waiting for some hint guys !!!

Regards
Gajendra

 

[xenou]

I was not sure if array formulas even work in Conditional Formatting....are you saying when you "hard code" the formula that it is entered in the CF criteria - but I'm curious does it actually work ?

What does this formula look like when it is finished - the use of so many variables makes it a bit cryptic.

What's the purpose of the conditional formatting here...another way to do this would be to enter the formula into a regular (hidden) column, then use the CF formula to simply refer to the result in that cell. That way you aren't dealing with so much processing in the CF itself.

It's not completely relevant but in CF formulas I like to use OFFSET() so that the formula is not affected adversely by deleted rows or columns that could ruin the references in the CF.

Alex.

 

[gaj_cod]

Hi Alex

Thanks for your response. But we are looking for CF via arrays because we have got significant improvement in the performance through arrays so that is why trying to give it a shot.

I am waiting for other experts also to put their thoughts into the same.

This is urgent guys !!!


Regards
Gajendra

 

[xenou]

I'd still recommend you post your formula the way it "really looks". Its just hard to see what that formula is...

Sometimes it works better to create the formula in a separate statement:

Dim strFormula as String
strFormula = "=RC*RC[1]"
Range("A2").FormulaR1C1 = strFormula