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
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)")
Thank you! It's working now :) However I think my loop is wrong. It keeps writing only "58" as i for my formula. Do you know why? I want it to count from 11 to 58, so the formula loops through every row from 11 to 58
I think because you have two loops that look to be doing the same thing, It is iterating the outer loop getting you the row you want then looping i 11 to 58 for each outer loop iteration. That means you are always left with 58 as that is the last one it deals with.
Thank you Warcupine! I got it to work now :)