Warm tip: This article is reproduced from stackoverflow.com, please click
excel vba

How do I write an WITH statement that loops through rows within the WITH-formula?

发布于 2020-03-27 10:23:49

I'm building an interactive Gantt chart with many rows (which is why I use VBA) and I'm having trouble with formatting my cells. Basically I just want the cells to have color based on an AND-formula. The formula is referring to other cells, so the formatting is not based on the cells own value. The tricky part is that my formula needs to change according to the specific row that it's looping through. I managed to build a code that correctly loops through each row, but I can't get the formula to change as well. Right now my code gives me an syntax error in the formula-part of my with-expression. I hope you guys can help!

I figured that the problem might be that the concatenate-trick might not work with with-statements. But I don't know how I can do the formatting in any other way.

Public Sub FormatTest()
Dim Sheet As Object
Dim Area As Range, r As Range
Dim i As Integer

Set Area = Sheets("Styregruppe - Tester").Range("H11:BK58")
For Each r In Area.Rows
    For i = 11 To 58
    With r.FormatConditions
    .Delete
        With .Add(Type:=xlExpression, Formula1:="=OG(("C" & i)<=H8;("D" & i)>=H8)")
            .Interior.Color = RGB(0, 176, 240)
            .StopIfTrue = False
        End With
    End With
 Next i
 Next r
End Sub
Questioner
Christina
Viewed
110
Warcupine 2019-07-03 22:42

This line: .Add(Type:=xlExpression, Formula1:="=OG(("C" & i)<=H8;("D" & i)>=H8)")

Has improper quotes you took the C out of the quotes so it thinks its a variable and the ampersand is within the quotes so is being interpreted as a string character and not as the concatenation character.

You want something like: .Add(Type:=xlExpression, Formula1:="=OG((C" & i & ")<=H8;(D" & i & ")>=H8)")