VBA Select Entire Row when cell is selected

bamaisgreat

Well-known Member
Joined
Jan 23, 2012
Messages
794
Do any you have away I can change the code below to have the entire row change to red instead of just the selected cell? Thanks as always

Code:
 Cells.Select
    Selection.FormatConditions.Add Type:=xlTextString, String:="(OBSOLETE", _
        TextOperator:=xlBeginsWith
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    ' adam test
    Dim test As Integer
    test = Selection.Row
    ' end test
    
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 255
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
 

Some videos you may like

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,695
Office Version
  1. 365
Platform
  1. Windows
Try
Code:
With Selection.EntireRow.FormatConditions(1).Interior
 
Last edited:

bamaisgreat

Well-known Member
Joined
Jan 23, 2012
Messages
794
Yongle, I tried adding the code you mentioned above but Im still only getting the cell to change to red instead of the entire row.
Code:
      Cells.Select
    Selection.FormatConditions.Add Type:=xlTextString, String:="(OBSOLETE", _
        TextOperator:=xlBeginsWith
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
 
    
  '  With Selection.FormatConditions(1).Interior
    With Selection.EntireRow.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 255
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    Range("C12").Select
 
End Sub
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,695
Office Version
  1. 365
Platform
  1. Windows
Delete any old format conditions that no longer apply
- every time you run macro containing .FormatConditions.Add another condition is added and they may conflict with each other

try this

Code:
Sub NewRule()
    With Cells
        .FormatConditions.Add Type:=xlExpression, Formula1:="=COUNTIF(1:1,""OBSOLETE"")>0"
        .FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
        With .FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .Color = 255
            .TintAndShade = 0
        End With
        .FormatConditions(1).StopIfTrue = False
    End With
End Sub

It should highlight the row if any cell value in that row = "obsolete"
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,108,509
Messages
5,523,312
Members
409,511
Latest member
hitesh222002

This Week's Hot Topics

Top