CODEWITHSUNDEEP
vbaexcelcolorsconditional-formatting
andrew
andrew

Reputation: 41

Deterine a change in cell color from conditional formatting

So my goal is to change the color of any string that has the word "Totals:" in it. Then use logic to Highlight the cells B1 and C1. My current code can change the color but when i use logic it thinks the cells aren't filled.

Sub Macro6()
'
' Macro6 Macro
'


   'ActiveWindow.SmallScroll Down:=-12
Range("A1:A381").Select
Selection.FormatConditions.Add Type:=xlTextString, String:="Totals:", 
TextOperator:=xlContains
'  


Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
'  With Selection.FormatConditions(1).Font
     '     .Color = -16383844
    '    .TintAndShade = 0
  '  End With
With Selection.FormatConditions(1).Interior
    .PatternColorIndex = xlAutomatic
    .Color = 13551615 '16777215 '
    .TintAndShade = 0
 End With
'Selection.FormatConditions(1).StopIfTrue = False
'lastrow = Sheet4.Cells(Sheet4.Rows.Count, "A").End(xlUp).Row



If Sheet1.Cells(9, 1).Interior.Color = 13551615 Then '16777215
MsgBox ("yes")
Else
MsgBox ("wrong")
MsgBox (Sheet4.Cells(6, 1).Interior.Color)
End If






End Sub

Upvotes: 1

Views: 268

Answers (2)

andrew
andrew

Reputation: 41

Thanks for the help! You saved me alot of time. This is what i ended up using encase anyone cares.i ended up noticing the last word was total so i could use the right function

Sub Macro6()

lastrow = Sheet4.Cells(Sheet4.Rows.Count, "A").End(xlUp).Row
Dim A As Integer
For N = 1 To  lastrow

A = 1
If Right(Sheet4.Cells(N, 1), 7) = "Totals:" Then
' MsgBox ("Found at" & Sheet4.Cells(N, 1))
'MsgBox (N)
Sheet4.Cells(N, 1).Interior.Color = 13551615

Else
'nothing
' MsgBox ("Found at" & Sheet4.Cells(N, 1))
' MsgBox (N)
End If
'A = A + 1
Next


End Sub

Upvotes: 0

user4039065
user4039065

Reputation:

You need to use the cell's .DisplayFormat property to retrieve the formatting aspects of a conditional formatting rule.

Option Explicit

Sub Macro1()
    With Worksheets("Sheet1")
        With .Range("A1:A381")
            .FormatConditions.Delete
            With .FormatConditions.Add(Type:=xlTextString, String:="Totals:", TextOperator:=xlContains)
                .Interior.Color = 13551615 '16777215
                .SetFirstPriority
                .StopIfTrue = False
            End With
        End With
    End With

    With Worksheets("Sheet1")
        MsgBox CBool(.Range("A9").DisplayFormat.Interior.Color = 13551615)
    End With
End Sub

It may be of worth to note that .DisplayFormat cannot be used within a user defined function (aka UDF).

Upvotes: 3

Related Questions