troubleshooting Question

Conditionally format row based on input cell

Avatar of avgplusguy
avgplusguyFlag for United States of America asked on
Windows 10Windows OSVBAMicrosoft ExcelMicrosoft Office
7 Comments1 Solution78 ViewsLast Modified:
Excel 365 Windows 10 VBA Conditional formatting
PLTest.xlsx is small sample with data values changed
Corporate monthly #s  tab usually < 1000 rows
place 10000 in F2 10% in G2
format each qualifying row yellow from row2 to last row
Part 1  Is it a total line?     If Left(c2,5) = "TOTAL"  
Part 2 Is it an account line?  isnumber(b2) Account
Macro would run with these default numbers
I would like for the user to be able to change numbers and if necessary rerun the macro

Sub CF_Var()
' Macro1 Macro
    Dim Lastrow As String
   Lastrow = CStr(wksht.Cells(2, "C").End(xlDown).Row)
    ActiveCell.FormulaR1C1 = "10000"
    ActiveCell.FormulaR1C1 = "0.1"
    Selection.NumberFormat = "0.00%"
    With Selection.Font
        .Name = "Century Gothic"
        .FontStyle = "Bold"
        .Size = 14
    End With
    With Selection.Font
        .Name = "Century Gothic"
        .FontStyle = "Bold"
        .Size = 14
     End With
     'IF left(c27,5) = "Total") start with c2 and check to last row.
'NEED LOGIC HERE to check all rows for TOTAL  
'Version TWO check if column B is a number
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
    End With

End Sub

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 7 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 7 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros