How do I highlight columns A thru P on a row that contains certain keywords in column C.

3wits
3wits used Ask the Experts™
on
I have a spreadsheet that contains subtotal rows.  In column C,  the words "100% Total" or "90% Total" or 75% Total" etc., appear.  I want to find the rows that contain the word Total in column C, then I want to highlight Columns A thru P in those particular rows.  I want to highlight with 25% gray.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Top Expert 2012

Commented:
The "truth" test is =FIND($C1,"Total")>0

So if the word Total exists anywhere in column C row 1, then you get true as a result, and thus the condition can apply formats to $A1:$P1.

I created a test for you, for the first 18 rows, demonstrating this.  You can just copy the A:P formats down as far as you'd like.

The trick is the $ in the FIND commandand the $ settings in the "Applies To, to ensure that evertyhing moves from row to row andn stays fixed in the columns that are set.

So click in C1, and set the conditional formatting using =FIND($C1,"Total")>0, then manage your conditional formatting and ensure Applies To is $A1:$P1.  Then you can copy formats down as long as you want (copying A1:P1 over the dataset you want this formatting for).

Her's a picture on how it was setup:

 demo
See attached,

Dave
highlight-Total-A-P-r1.xlsx

Author

Commented:
Thanks Dave.
I tried it, IT WORKS, but we need to make an adjustment.  The content in C will say "100% Total" or "90% Total" or" 75% Total".  Therfore, the rows that I want highlighted contain the word "Total" but also has other text. It's like we need a wildcard or something. Can you make an adjustment for this.

ALSO, is there a way to have it insert a blank line under the row we just highlighted?

This is what I'm doing.  I'm writing a macro that
     1)sets filter
     2) sort A
     3) sort C
     4) subtotal several columns based on Column C
     5) remove filters

 I would love to add these 2 steps after #4 above
     5) highlight the subtotal rows
     6) insert a blank line under the subtotals

The reason for this macro:  Percentages will change in column C.  The macro is needed for it to move to its correct location.

Thanks.
 
test--8-5-11.xlsm
Most Valuable Expert 2012
Top Expert 2012

Commented:
Before we go there, first, let's take a look at the formula for conditional formatting:

=FIND($C1,"Total")>0 <- My bad, it should be rephrased to where the FIND_TEXT is the first parameter.

It should be =FIND("Total",$C1)>0

This means that if the text Total is ANYWHERE in the string, the conditional setting would be set to TRUE.

See examples in the attached.

highlight-Total-A-P-r2.xlsx
CompTIA Network+

Prepare for the CompTIA Network+ exam by learning how to troubleshoot, configure, and manage both wired and wireless networks.

Author

Commented:
Thanks - that works.  Now how do I get that in my macro?
Most Valuable Expert 2012
Top Expert 2012

Commented:
For your second part to insert row, see code at bottom, thus:
 
Application.EnableEvents = False
    
    Set fRange = Range("C:C").Find(what:="Total", LookIn:=xlValues, lookat:=xlPart)
    
    If Not fRange Is Nothing Then
        firstAddress = fRange.Address
        Set insertRow = fRange.Offset(1, 0)
        Do
        
            Set fRange = Range("C:C").FindNext(fRange)
            If Not fRange Is Nothing Then
                Set insertRow = Union(insertRow, fRange.Offset(1, 0))
            End If
        Loop While Not fRange Is Nothing And firstAddress <> fRange.Address
    
        insertRow.EntireRow.Insert
    End If
    Application.EnableEvents = True
    Range("A1").Select

Open in new window


See attached file revision with macro changes.

Enjoy!

Dave
test--8-5-11.xlsm

Author

Commented:
Thank you so much Dave.  We are getting close.  It worked.  I changed two percentages, CTRL-t and it worked.  However, I changes two percentages again and I got an error message.

Deb
Most Valuable Expert 2012
Top Expert 2012
Commented:
I'm not sure what you're alluding to.  Every time you run the macro, it inserts the blank line.  You'll need to think through the logic and adjust based on how you believe the data will be used.  Perhaps delete any blank rows at the start, then insert after?

    For Each myCell In Range("C3:C70")
        If Evaluate("COUNTA(" & myCell.EntireRow.Address & ")") = 0 Then
            If Not delRow Is Nothing Then
                Set delRow = Union(delRow, myCell)
            Else
                Set delRow = myCell
            End If
        End If
    Next myCell
   
    If Not delRow Is Nothing Then
        delRow.EntireRow.Delete
    End If

See attached,

PS - This is going well beyond the original scope of the question.

Dave
test--8-5-11.xlsm

Author

Commented:
I'm sorry.  You are correct.  I'm not sure what I was doing the first time.  Thank you so much for your help.  Your advice has been excellent.

Author

Commented:
This help was great

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial