Hi all!
I am trying to create variable conditional formatting. User will determine range and rules in sheet called CondFormat. This is how it looks like, is it possible to apply it with VBA? I tried to create macro, but I cannot create a way to get values from cells (like in which sheet is should be applied). Colors are used in hex format, so I added "&H" to cells with color (column 4 and 5). Any ideas how to fix it / achieve it?
I am trying to create variable conditional formatting. User will determine range and rules in sheet called CondFormat. This is how it looks like, is it possible to apply it with VBA? I tried to create macro, but I cannot create a way to get values from cells (like in which sheet is should be applied). Colors are used in hex format, so I added "&H" to cells with color (column 4 and 5). Any ideas how to fix it / achieve it?
VBA Code:
Sub CondForm()
Dim rngStart As Range
Dim rngStop As Range
Dim Cond1 As FormatCondition
Dim Cond2 As FormatCondition
Dim Ws As Worksheet
Dim LastRow As Integer
LastRow = Worksheets("CondFormat").Range("A" & Rows.Count).End(3).Row
'Remove old conditional formatting
For Each Ws In ThisWorkbook.Sheets
Ws.Cells.FormatConditions.Delete
Next
'Set new formatting
Dim x As Integer
For x = 2 To LastRow
'HOW TO SET THIS WORKSHEET FROM CELL VALUE?
Ws = Worksheets(Cells(x, 1).Value)
rngStart = Cells(x, 6).Value
rngStop = Cells(x, 7).Value
Ws.Range(rngStart, rngStop).FormatConditions.Add xlExpression, Formula1:= _
Cells(x, 3).Value
Ws.Range(rngStart, rngStop).FormatConditions.Interior.Color = Cells(x, 4).Value
Ws.Range(rngStart, rngStop).FormatConditions.Font.Color = Cells(x, 5).Value
Next x
End Sub