Highlight Row based on Conditional Formatting

[pkew22]

I have data currently in an Excel spreadsheet in Columns C:Q
Columns are going to be added but Column C will always be the starting point with C6 being the first data cell. Row 5 has headings and no cell in the data area in row 5 is blank.
Row 6 has data but there are cells which are blank.

I would like to select data from C6 to the last row in the next to last column. (Currently I would like to select C:P as P is the second last column)
I have a tag column 6 columns to the right of P that has various codes. IF there is an X, I would like to highlight the entire row.

'If Close = Y , then shade row from C:T (T=Sen All Funds)
Dim LastRow As Integer
'Identify Data Rows
LastRow = Range("A" & Rows.Count).End(xlUp).Row

'Select data from A4 to last data row in T (U= Row#)
Range("C4:P" & LastRow).Select   'I WOULD LIKE TO SELECT THIS RANGE WIHTOUT USING P
'Conditional format -- shade A:K blue if two conditions are met
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=rc[6]=""X"""  'IF THERE IS AN X SIX COLS TO THE RIGHT OF THE LAST COLUMN SELECTED, THE ROW SHOULD BE HIGHLIGHTED
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 14539203 'grayish blue
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    Range("K6").Select

I hope that this is not too complicated a question and someone can help. I am at sixes and sevens on how to select the data that I require and then highlight the row that corresponds to the cell that is coded X. Thank you.

 

[Jeffrey Mahoney]

I have used macros to alter CFs on a sheet, but find that it's annoying because if I change the CF manually I also have to change it in the macro. Here is the formula I used and the macro I recorded.

=UPPER($C6)="X"

Range("C6:P16").Select
    Cells.FormatConditions.Delete
    Range("C6:P16").Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=UPPER($C6)=""X"""
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent1
        .TintAndShade = 0.399945066682943
    End With
    Selection.FormatConditions(1).StopIfTrue = False

 

[Jeffrey Mahoney]

You of course would change the range to what you need.

 

[pkew22]

Thank you. This is tremendously helpful. I am very close to getting to the desired solution.
Is there any way that I can avoid saying $V4 but rather do it with using offset?

'Shade Row if there is agreement -- Y in Close column
Dim LastRow As Integer
LastRow = Range("B" & Rows.Count).End(xlUp).Row

'Select B2:LastCol-2
Range("C4:" & Left(CStr(Range("B2").End(xlToRight).Offset(0, -1).Address), Len(CStr(Range("B2").End(xlToRight).Address)) - 1) & Range("A" & Rows.Count).End(xlUp).Row).Select

'If Close is Y then Conditional Format
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=UPPER($V4)=""Y"""   
'If I caould use offset or something like that but that would still get $V4 or a column 6 to the right of the selection that would be helpful 

    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 16777164
        .TintAndShade = -0
    End With
    Selection.FormatConditions(1).StopIfTrue = False

 

[Jeffrey Mahoney]

Any formula you can write in a cell can be applied in Conditional Formatting. The result has to be either TRUE or FALSE.