More Sophisticated Conditional Formatting

[FrankNJohnson]

Friends:

I'm trying to implement some conditional formatting that I think is a bit different than a normal implementation and I'm hoping that folks here might be able to help me.

I have a spreadsheet that I use to present web analytics and ecommerce performance to the rest of our executive team. Here's an example:

I have the values updating from another worksheet in the file than what I'm showing here based on the start and end dates I choose from the date pickers in rows 1 and 2. Normally, I'm presenting week-over-week and year-over-year comparisons (thus the "WK/WK CHANGE" and "YR/YR CHANGE" headings near the top of columns G and H).

I also have columns A through H formatted with bold typeface and a purple background whenever I select a cell in a row (the current implementation of conditional formatting doesn't format the entire row although I can pick any cell in the row - it only formats columns A through H).

I do that using this formula:

=ROW(A5)=CurrentRow2

with that formatting rule applying to these ranges:

$A$5:$H$21,$A$24:$H$34,$A$37:$H$44,$A$47:$H$54,$A$57:$H$63,$A$66:$H$72,$A$75:$H$80,$K$39:$P$43,$K$49:$P$53

(obviously, some of those ranges are outside the screenshot, but I trust you'll get it)

and CurrentRow2 that refers to "=5"

I want to enhance the conditional formatting so that:

1) if I select a cell in column I, only the corresponding cells in columns A-D and G will be formatted;
2) if I select a cell in column J, only the corresponding cells in columns A, D-F, and H will be formatted; and,
3) if I select a cell in column K, the corresponding cells in columns A-H will be formatted (as they are now if I select any cell in a row).

Is that functionality possible?

Thanks in advance for any advice anyone can offer - it's much appreciated!

 

[Candyman8019]

I think I get what you're after. Give something like this a try. Add the following VBA code to the sheet you want formatted:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  
  If Not Intersect(Target, Range("I:I")) Is Nothing Then
    Application.ScreenUpdating = False
    'Clear the color of all cells
    Rows("5:500").Interior.ColorIndex = 19
        'highlight required cells
        Range("A" & Target.Row & ":D" & Target.Row).Interior.ColorIndex = 4
        Range("G" & Target.Row).Interior.ColorIndex = 4
    Application.ScreenUpdating = True
  End If
  
  If Not Intersect(Target, Range("J:J")) Is Nothing Then
    Application.ScreenUpdating = False
    'Clear the color of all cells
    Rows("5:500").Interior.ColorIndex = 19
        'highlight required cells
        Range("A" & Target.Row).Interior.ColorIndex = 4
        Range("D" & Target.Row & ":F" & Target.Row).Interior.ColorIndex = 4
        Range("H" & Target.Row).Interior.ColorIndex = 4
    Application.ScreenUpdating = True
  End If
  
  If Not Intersect(Target, Range("K:K")) Is Nothing Then
    Application.ScreenUpdating = False
    'Clear the color of all cells
    Rows("5:500").Interior.ColorIndex = 19
        'highlight required cells
        Range("A" & Target.Row & ":H" & Target.Row).Interior.ColorIndex = 4
    Application.ScreenUpdating = True
  End If

  If Intersect(Target, Range("I:K")) Is Nothing Then
    Application.ScreenUpdating = False
    'Clear the color of all cells
    Rows("5:500").Interior.ColorIndex = 19
    Application.ScreenUpdating = True
  End If
End Sub