Consulting

Results 1 to 3 of 3

Thread: highlight entire rows when two cells in row are populated

  1. #1
    VBAX Newbie
    Joined
    Jun 2014
    Posts
    1
    Location

    highlight entire rows when two cells in row are populated

    Hi. I'm new to VBA Code so thank you in advance for your help.
    I am trying to highlight an entire row in excel if two conditions are met. I found this code that answered the question if one condition was met. But How would I add an additional criteria if I need values in column F to also be evaluated?
    So for instance column G would need to equal "Break Out" AND Column F would need to be like "Emergency" or "Urgent".

    Additionally as exampled above the values in column F (to be considered for highlighting entire row) should be "in ("Emergency", "Urgent")". I know VBA doesn't accept the IN statment, so how would I work a Case into the below code?
    credit to mdmackillop for the below code:
     
    Sub Macro1() 
        Const TEST_COLUMN As String = "D" '<=== change to suit
        Dim LastRow As Long 
        Dim cell As Range 
        sSheetName = ActiveSheet.Name 
    With Worksheets(sSheetName) 
            LastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row 
    For Each cell In Range("G2:G" & LastRow) 
                If cell.Value = "Break Down" Then 
                    cell.Offset(, -6).Resize(, 21).Interior.ColorIndex = 39 
                ElseIf cell.Value = "PM/SM Call" Then 
                    cell.Offset(, -6).Resize(, 21).Interior.ColorIndex = 43 
                Else 
                    cell.EntireRow.Interior.ColorIndex = xlNone 
                End If 
            Next 
        End With 
    End Sub 

  2. #2
    VBAX Tutor
    Joined
    Mar 2014
    Posts
    210
    Location
    Get away from ELSEIF constructs...SELECT CASE is easier to read

    select case cell.value
       case  "Break Down" 
     cell.Offset(, -6).Resize(, 21).Interior.ColorIndex = 39 
       case  "PM/SM Call" 
     cell.Offset(, -6).Resize(, 21).Interior.ColorIndex = 43 
       case Else 
      cell.EntireRow.Interior.ColorIndex = xlNone 
    End select
    
    'or 
    
    select case TRUE  'now you can evaluate anyting
       case  cell.value = "Break Down" AND cell.range("F" & x).value = "my value"      'COLUMN F
           cell.Offset(, -6).Resize(, 21).Interior.ColorIndex = 39 
       case  cell.Value = "PM/SM Call" 
          cell.Offset(, -6).Resize(, 21).Interior.ColorIndex = 43 
       case Else 
          cell.EntireRow.Interior.ColorIndex = xlNone 
    End select

  3. #3
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,875
    You could use conditional formatting.
    Select Cells A2 to Uxxx, making sure that you select from top to bottom so that the active cell is in row 2 while you do the following, then in conditional formatting use a formula to determine… and use the formula:
    =AND($G2="Break Out",OR($F2="Emergency",$F2="Urgent"))
    then select the format of the highlighting, OK.
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

Posting Permissions

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