Help Changing Formula in VBA

[markjdm]

Hi Guys,

I tried searching for this but am not what function/feature it may be referred to.

I'm trying to do something similar to below but I can't seem to find the correct syntax to get it working. Very new to VBA so still finding my way around it.

What I'm tyring to do is change the format with the below VBA script but want to loop changing my formula in the condition similar to this when we enter data

"=Calculations!$I$2 on first cell then Calculations!$I$3 and so forth"

Sub CondFormat()'


Dim col As Integer
col = 6


For i = 13 To 50
Cells(i, col).Select
Selection.FormatConditions.add Type:=xlExpression, Formula1:= "=Calculations!$I$2)"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent2
.TintAndShade = 0.799981688894314
End With
Selection.FormatConditions(1).StopIfTrue = False

Next i

End Sub

Thanks!

 

[ParamRay]

.
.

Try this code instead:

Sub SetFormatCondition()
    
    Dim FCon As FormatCondition
    
    Set FCon = Range("F13:F50").FormatConditions.Add( _
        Type:=xlExpression, _
        Formula1:="=F13=Calculations!I2")
        
    With FCon
        .SetFirstPriority
        
        With .Interior
            .ThemeColor = xlThemeColorAccent2
            .TintAndShade = 0.8
        End With
        
        .StopIfTrue = False
    End With
    
End Sub

 

[markjdm]

Thanks mate, this works with formatting everything based on this formula. "=F13=Calculations!I2"

I was hoping to make something that for each cell it will change the formula too. Sorry for not being clear, about it.

I was hoping to do a conditional format similar to if I did it manually.

Cell F3 = format will be based on formula =Calculations!I2
Cell F4 = format will be based on formula = Calculations!I3
Cell F5 = format will be based on formula = Calculations!I4

Basicall as each cell goes down to be formatted, it will also change the conditional format formula with a new cell location?

Hope that makes sense.Ü

Cheers,
Mark

 

[ParamRay]

Thanks mate, this works with formatting everything based on this formula. "=F13=Calculations!I2"

I was hoping to make something that for each cell it will change the formula too. Sorry for not being clear, about it.

I was hoping to do a conditional format similar to if I did it manually.

Cell F3 = format will be based on formula =Calculations!I2
Cell F4 = format will be based on formula = Calculations!I3
Cell F5 = format will be based on formula = Calculations!I4

Basicall as each cell goes down to be formatted, it will also change the conditional format formula with a new cell location?

Hope that makes sense.Ü

Cheers,
Mark

What you are asking for is achieved by the code I originally posted...