Consulting

Results 1 to 8 of 8

Thread: Change Row Color based on Cell String Match

  1. #1
    VBAX Newbie
    Joined
    Dec 2013
    Posts
    3
    Location

    Change Row Color based on Cell String Match

    Alright, I realize that this is a fairly commonly asked question, but I've tried my best to figure this out on my own.

    I am creating a protected worksheet for my team to use. So far, this has been a hack and slash job as I am far from proficient in Excel, but I have managed to make it work to some extent. However, I have realized that many individuals are unable to follow simple instructions, and thus, keep "breaking" my spread sheet. Thus, I've set off to create a "fool proof" system to avoid these problems.

    The sheet seeks to track how long a user spent on particular pieces of data. There are multiple types of documents to track.

    One column, F, specifies the type of document to enter. Users enter a string value of VD, RM, FA, NFA, or QC, or some combination of them.

    If the user has put FA or NFA in column F, I need that entire row to be highlighted. The color I need is "16764108"

    Sub Highlight_Apps()
    '
    ' Highlight_Apps Macro
    '
    ' Keyboard Shortcut: Ctrl+r
    '
        Cells.FormatConditions.Delete
         
         
         
        Range("A6:J205").Select
        Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
            "=OR(NOT(ISERROR(SEARCH(""FA"", F6:F205))))"
        Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
        With Selection.FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .Color = 16764108
            .TintAndShade = 0
        End With
        Selection.FormatConditions(1).StopIfTrue = False
        
        
        
    End Sub
    This code *works*... but I am only seeing the corresponding cell in column A receive the proper color. The rest remain unchanged.

    Formatting needs to apply from A6:J205 (A1:J5 are just a header and other necessary calculations that are currently functioning).

    I understand that with this macro I will have to ask users to enable macros as well as press a key combination (I've been using CTRL + R) prior to printing, but I can only hand hold so much.

    I appreciate any and all help on this matter! I am leaving the office soon, but I will try to answer from home as well.

    Note:
    Sometimes not every cell will be filled in within the row, which from what I have read may make this more complicated, please keep this in mind if this changes your code.

  2. #2
    if you are going to use conditional formatting then you should not require any vba code any way, and probably does not require macros enabled, but will not set colour to row

    you can use application events to save user pressing buttons like
    Private Sub Worksheet_Change(ByVal Target As Range)
      If Target.Column = 6 Then
       
       If Target = "FA" Or Target = "NFA" Then Target.EntireRow.Interior.Color = 16764108 Else Target.EntireRow.Interior.ColorIndex = xlColorIndexNone
      End If
    
    End Sub

  3. #3
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    Conditional formatting is the way to go.

    Select A6:J205
    Ribbon Home>Styles>Conditional Formatting>New Rule
    Select 'Use a formula to determine which cells to format'
    Input a formula of =OR($F6="FA",$F6="NFA")
    Click Format...
    Select the Fill tab
    Choose your colour
    Then OK out
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  4. #4
    Conditional formatting is the way to go.
    i thought so but i did not see how to set colour to entire row

  5. #5
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    He doesn't need, nor want, to colour the entire row, his data only extends to column J. But the whole row is just as easy, just select whole rows.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  6. #6
    VBAX Newbie
    Joined
    Dec 2013
    Posts
    3
    Location
    Thanks to those that have replied so far! Much appreciated.

    Quote Originally Posted by xld View Post
    He doesn't need, nor want, to colour the entire row, his data only extends to column J. But the whole row is just as easy, just select whole rows.
    Correct, I don't need the *entire* row covered, just from A through J, when the string "FA" is found.

    Currently, this is what my spreadsheet looks like. I had to make sure I was able to clear out some of the company data before posting this.

    HELP.jpg

    Final Edit: Alright, so, I do believe I want to continue to use Conditional Formatting, which is what my macro attempted to do. However, I need to macro in order to REAPPLY the conditional formatting that users may have messed up with copy and paste without choosing Paste Values.
    Last edited by Stuart; 12-30-2013 at 08:06 AM.

  7. #7
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    Public Sub ApplyCF()
    Const FORMULA_CF As String = "=OR($F7=""FA"",$F7=""NFA"")"
    Dim rng As Range
    Dim i As Long
    
        With ActiveSheet
        
            Set rng = .Range(.Range("A7"), .Range("A7").End(xlDown)).Resize(, 10)
            With rng
            
                For i = .FormatConditions.Count To 1 Step -1
                
                    .FormatConditions(i).Delete
                Next i
            
                .FormatConditions.Add Type:=xlExpression, Formula1:=FORMULA_CF
                .FormatConditions(1).Interior.ColorIndex = 40
                .FormatConditions(1).StopIfTrue = False
            End With
        End With
    End Sub
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  8. #8
    VBAX Newbie
    Joined
    Dec 2013
    Posts
    3
    Location
    Thank you so much!

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •