CODEWITHSUNDEEP
excelvba
Pom
Pom

Reputation: 43

How to apply conditional formatting using cell values?

I am trying to create variable conditional formatting. User will determine range and rules in sheet called CondFormat.

How can I 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, saw it on some forum that hex needs to be used with &H). How do I set worksheet from cell value? I wasn't able to fix this line, so I don't know if the rest of my code works (first time using For Next).

Variable Formatting

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

Upvotes: 0

Views: 183

Answers (1)

FaneDuru
FaneDuru

Reputation: 42236

Please, try the next scenario. Firstly, run it on a new workbook. The code is adapted to work on Active workbook. There must be a sheet named "Config", where the (correct) formulas and HEX colors must exist, another one named "BusDep_cf", able to keep strings based on which the newly applied conditional formatting works. The following code will format only the used range of the sheets placed in arrSh array:

Sub CondForm()
 Dim Ws As Worksheet, ws1 As Worksheet, LastRow As Long, rngF As Range, arrSh, El
 
 arrSh = Array("BusDept", "Test") 'place here the sheets name to be formatted

 Set Ws = ActiveSheet 'the "Config" one...
 If Ws.name <> "Config" Then MsgBox "Wrong activate cell..." & vbCrLf & _
                           "Please, activate ""Config"" sheet.", vbInformation, "Wrong sheet activation": Exit Sub
 LastRow = Ws.Range("C" & Ws.rows.count).End(xlUp).row 'last row in Config

Dim x As Long
For Each El In arrSh                  'iterate between the sheets array elements
    Set ws1 = Worksheets(El)    'set the sheet object
    Set rngF = ws1.UsedRange 'set its used range
    With rngF
        .FormatConditions.Delete 'delete the previous format
        For x = 2 To LastRow
                .FormatConditions.Add xlExpression, Formula1:=Ws.cells(x, 3).Value 'add a new format condition
                        
                With .FormatConditions(.FormatConditions.count) 'configure the added condition:
                    .Interior.Color = CLng(cells(x, 4).Value)
                    .Font.Color = CLng(cells(x, 5).Value)
                End With
        Next x
     End With
 Next El
 MsgBox "Ready..."
End Sub

I will place a link to a transfer site, where from to download the testing workbook to work with it activated. It contains only 4 conditions, but you can add as many as you need.

Please, test the above suggested solution and send some feedback.

Upvotes: 1

Related Questions