Changing font color of top cells if cell in column is selected

[Young Grasshopper]

Hi world!

I'm trying to write a code where the font color in row 1 and 2 changes color if any cell in the rows underneath it I selected and change back again when a cell outside of range is selected, but it don't really work..
Main problem now is that color of C:F f.ex only changes if i go outside of all three ranges, and not just when I go outside C:F.
The other problem is that this needs to be repeated for a lot more ranges; C:F, H:K, M:P, R:U, W:Z etc. (Not a problem if it's easier to do it C:G, H:I, M:Q and not skip the one column btw".
So there is probably a smarter way to write this, than writing an IF statement 100 times?

This is what i have now:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Selection.Count = 1 Then
    If Not Intersect(Target, Range("C:F")) Is Nothing Then
        Range("C1:E2").Font.Color = RGB(254, 109, 37)
        Else
        If Not Intersect(Target, Range("H:K")) Is Nothing Then
        Range("H1:J2").Font.Color = RGB(254, 109, 37)
        Else
         If Not Intersect(Target, Range("M:P")) Is Nothing Then
        Range("M1:P2").Font.Color = RGB(254, 109, 37)
        Else
        Range("B1:AA2").Font.Color = RGB(128, 128, 128)
   
    End If
    End If
    End If

End If
End Sub

Would appreciate any help

 

[DanteAmor]

You can use a Conditional Formatting like this:

Libro1
	A	B	C	D	E	F	G	H	I	J	K	L
1
2
3
4
Hoja1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C1:F2,H1:K2,M1:P2,R1:U2,W1:Z2	Expression	=COLUMN()=CELL("COLUMN")	text	NO

In your sheet events, change your code to this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.ScreenUpdating = True
End Sub

To add a Conditional Formatting:
1. On the Home tab, click Conditional Formatting > New Rule.
2. Select Use a formula to determine which cells to format.

3. In the Format values where this formula is true box, type the formula:

=COLUMN()=CELL("COLUMN")

4. Click custom format.

5. In the Format Cells dialog box, click the Fill tab.
6. Select the color you want.
7. Click OK. Click ok.
8. In the Applies to: box, type:

=$C$1:$F$2,$H$1:$K$2,$M$1:$P$2,$R$1:$U$2,$W$1:$Z$2

(Or all the range you need.)
9. Click OK.
Done, press any cell.

 

[Young Grasshopper]

Hi Dante,

Thank you
But this did not seem to work.. Copied your setup, but no reaction sadly..
Anyhow, this seems to only change the top cell in the column your in? I need to change the top cells in the range I'm in.
So for range C:F, I need to change the range of C1,C2, E1, E2 no matter if I am in C11 or F15 etc.

 

[dmt32]

So for range C:F, I need to change the range of C1,C2, E1, E2 no matter if I am in C11 or F15 etc.

Hi,
see if this code does what you want

Place in your worksheets code page

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim rng         As Range, Area As Range
    
    'specify the range areas as required
    Set rng = Me.Range("C:F, H:K, M:P, R:U, W:Z")
    
    If Selection.Count = 1 Then
        For Each Area In rng.Areas
            Area.Rows("1:2").Font.Color = RGB(128, 128, 128)
            If Not Intersect(Target, Area) Is Nothing Then
                Me.Cells(1, Area.Columns(1).Address).Resize(2, Area.Columns.Count - 1).Font.Color = RGB(254, 109, 37)
            End If
        Next Area
    End If
    
End Sub

Change the range areas shown in BOLD as requred

Dave

 

[DanteAmor]

Forget conditional formatting apply the following code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Dim n As Long
  
  If Target.CountLarge = 1 Then
    Range("B1:AA2").Font.Color = RGB(128, 128, 128)
    n = 0
    If Not Intersect(Target, Range("C:Z")) Is Nothing Then
      Select Case Target.Column Mod 5
        Case 0: n = Target.Column - 2
        Case 1: n = Target.Column - 3
        Case 3: n = Target.Column
        Case 4: n = Target.Column - 1
      End Select
      If n > 0 Then
        Cells(1, n).Resize(2, 3).Font.Color = RGB(254, 109, 37)
      End If
    End If
  End If
End Sub

 

[Young Grasshopper]

Perfect, both solutions work great!
Thanks for the help guys, and enjoy the rest of your day