Create Conditional Formatting in VBA based on value AND formatting of a cell

[Gergely]

Hello Dear Members,

This is my first post here, and I can not post attachments, so I try to explain clearly what am I after.

I have a "data" sheet with a list of equipment. Each has a number (ID) in column A and a toolstatus in column B. Toolstatus can be selected from a data validation list. This list is on "list" sheet in column A, and contains similar possibilities like: "in factory", "finished", "in prod". etc. These cells on "list" sheet have background formatting: solid fill or gradient fill, set manually.

I want to let the user change the list of possible values and also the backgrounds. When he runs the macro, it should create Conditional Formatting for Data!B:B column as much as many entries are in the list!A:A column and get the formatting form the respective cell.

(Eg.: Take the first entry in the list and create a conditional formatting based on this cell. If the cell value is "in prod" and the background is green-white gradient fill, then it should create a Conditional Formatting Rule for data!B:B that if the value is "in prod" then set the background to green-white gradient fill.
Take the next cell and create the next conditional formatting (either it is solid fill or gradient fill)
Do this for the rest of the cells with value in list!A:A


This is what I have so far:

Sub updatecondformatting()
Dim lastrow As Long
Dim counter As Long
Dim colorind As Integer
Dim intpattern As Integer
Dim intgraddegree As Integer
Dim intgradonecolor As Long
Dim intgradonetintandshade As Integer
Dim intgradtwocolor As Long
Dim intgradtwotintandshade As Integer
Dim intpatterncolorindex As Long
Dim intcolor As Long
Dim inttintandshade As Long
Dim intpatterntintandshade As Long
Dim statusvalue As String
Dim formulastring As String
 
lastrow = Range("LIST").Rows.Count + 1
Cells.FormatConditions.Delete
Sheets("list").Activate
For counter = 2 To lastrow
statusvalue = Range(Cells(counter, 1), Cells(counter, 1)).Value
If Range(Cells(counter, 1), Cells(counter, 1)).Interior.Pattern = xlSolid Then
    With Range(Cells(counter, 1), Cells(counter, 1)).Interior
        intpattern = .Pattern
        intpatterncolorindex = .PatternColorIndex
        intcolor = .Color
        inttintandshade = .TintAndShade
        intpatterntintandshade = .PatternTintAndShade
    End With
    Range("DATA").FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:=statusvalue
    
    With Range("DATA").FormatConditions(counter - 1).Interior
        .PatternColorIndex = intpatterncolorindex
        .Color = intcolor
        .TintAndShade = inttintandshade
        .PatternTintAndShade = intpatterntintandshade
    End With
        
    Range("DATA").FormatConditions(counter - 1).StopIfTrue = False
ElseIf Range(Cells(counter, 1), Cells(counter, 1)).Interior.Pattern <> xlSolid Then
    With Range(Cells(counter, 1), Cells(counter, 1)).Interior
        intpattern = .Pattern
        intgraddegree = .Gradient.Degree
        intgradonecolor = .Gradient.ColorStops.Item(1).Color
        intgradonetintandshade = .Gradient.ColorStops.Item(1).TintAndShade
        intgradtwocolor = .Gradient.ColorStops.Item(2).Color
        intgradtwotintandshade = .Gradient.ColorStops.Item(2).TintAndShade
    End With

    Range("DATA").FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:=statusvalue
    With Range("DATA").FormatConditions(counter - 1).Interior
        .Pattern = intpattern
        
        .Gradient.Degree = intgraddegree
        .Gradient.ColorStops.Add(0).Color = intgradonecolor
        .Gradient.ColorStops.Add(0).TintAndShade = intgradonetintandshade
        .Gradient.ColorStops.Add(1).Color = intgradtwocolor
        .Gradient.ColorStops.Add(1).TintAndShade = intgradtwotintandshade
    End With
    Range("DATA").FormatConditions(counter - 1).StopIfTrue = False

End If
Next counter
Sheets("data").Activate
End Sub

This works fine to create the conditional formatting rules, although they do not work correctly. Some of the fields (only with gradient fill) seems to update only when the value is selected or the workbook is closed and reopened. Solid fill works perfectly.

It is also strange that if you click conditional formatting>manage rules the rules for solid fill you see the rules ok. On the other hand the rules with gradient fill seems to have "white" or "no background" formatting. Only if you double click one of them and then >format... then on fill tab click >"fill effects..." then you can see the gradient fill settings OR on the fill tab sample field, and in "fill effects" not. It seems that I miss something here...

What do you think?
Have a nice day!

Gergely

PS: Is there a way I can post my sample *.xlsm?
Ps2: I use Excel 2010 under Windows7

 

[Gergely]

Can anyone please show me a direction, where to search for the solution?

Have a nice day!

Gergely