Conditional Formatting dynamic?. SelectionChange

Arnolf

Board Regular
Joined
Sep 18, 2005
Messages
78
Hello,

How can insert a formula in the conditional formatting depending on the number row every time I select a cell?
I mean, every time I select a cell in the range E2:E10, the formula should change in the conditional formatting for the range A2:A14.

Example:
If I select E2, the Formula is in the Conditional Formatting for the range A2:A14 should be:
=(A2<>"")*(COUNTIF(A$2:A2,A2)<=COUNTIF(F$2:J$2,A2))
If I select E3, the Formula is in the Conditional Formatting for the range A2:A14 should be:
=(A2<>"")*(COUNTIF(A$2:A2,A2)<=COUNTIF(F$3:J$3,A2))
If I select E4, the Formula is in the Conditional Formatting for the range A2:A14 should be:
=(A2<>"")*(COUNTIF(A$2:A2,A2)<=COUNTIF(F$4:J$4,A2))
and so on for the other cells. The variable is the number of the active row.


The range E2:E10 is only an example. My real range will be E2:E1500.


I tried to do it with the next code. Of course, it is wrong and doesnt work (just to give an idea, what I need to do).
Is it a better way to do it?
rgds,
Arnolf.


Code:
Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim b As Long
    If Intersect(Target, Range("E2:E10")) Is Nothing Then
    Else
        b = Target.Row
    End If
    If Not Intersect(Target, Range("E2:E10")) Is Nothing Then
        Range(Target.Offset(0, -4), Target.Offset(12, -4)).FormatConditions.Delete
        If Not IsEmpty(Target) Then
            Range(Target.Offset(0, -4), Target.Offset(12, -4)).FormatConditions.Delete
            Range(Target.Offset(0, -4), Target.Offset(12, -4)).FormatConditions.Add Type:=xlExpression, Formula1:= _
                                                                                    "=(A2<>"")*(COUNTIF(A$2:A2,A2)<=COUNTIF(F$" & b & ":J$" & b & ",A2))"
            Range(Target.Offset(0, -4), Target.Offset(12, -4)).FormatConditions(1).Interior.ColorIndex = 33
        End If
    End If
End Sub
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

Von Pookie

MrExcel MVP
Joined
Feb 17, 2002
Messages
13,686
I'm not sure if this is what you're wanting, but it works for me:

Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Not Intersect(Target, Range("E2:E1500")) Is Nothing Then
    With Range("A2:A14")
        With .FormatConditions
            .Delete
            .Add Type:=xlExpression, Formula1:= _
            "=($A$2<>"""")*(COUNTIF($A$2:$A$2,$A$2)<=COUNTIF($F$" & Target.Row & ":$J$" & Target.Row & ",$A$2))"
        End With
        .FormatConditions(1).Interior.ColorIndex = 33
    End With
End If

End Sub

If I tried it without the absolute references in the formula, it was putting some weird ranges in. Added the '$' signs and worked right away.
 

Arnolf

Board Regular
Joined
Sep 18, 2005
Messages
78
Thank you so much Kristy!!!!
It certainly works.
Only I have a little problem with the absolute references
"=($A$2<>"""")*(COUNTIF($A$2:$A$2,$A$2)<=COUNTIF($F$" & Target.Row & ":$J$" & Target.Row & ",$A$2))"

I changed to:
"=($A$2<>"""")*(COUNTIF($A$2:$A2,$A2)<=COUNTIF($F$" & Target.Row & ":$J$" & Target.Row & ",$A2))"
But, doesnt work.
Any way to solve it?.

This is what I am trying to achieve:


If the cursor is in E2
cformat.xls
ABCDEFGHIJ
1
2116646
328233
43191666
54151239
65101234
760
860
970
Sheet1


If the cursor is in E3,... and so on for the others
cformat.xls
ABCDEFGHIJ
1
2116646
328233
43191666
54151239
65101234
760
860
970
1060
119
123
1311
Sheet1
 

Von Pookie

MrExcel MVP
Joined
Feb 17, 2002
Messages
13,686
Yeah, that's what it was doing to me--if I didn't have the absolute references in, it was using references of IS65534, etc. in the formula.

I have no idea why :confused:
 

Arnolf

Board Regular
Joined
Sep 18, 2005
Messages
78
Very quick reply Kristy. :biggrin:
Thank you so much for your concern.
Hope somebody else find why that happen.
rgds,
Arnolf
 

Watch MrExcel Video

Forum statistics

Threads
1,118,458
Messages
5,572,249
Members
412,451
Latest member
newbie22922792
Top