Conditional Formatting dynamic?. SelectionChange

[Arnolf]

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.

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

 

[Von Pookie]

I'm not sure if this is what you're wanting, but it works for me:

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]

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
	A	B	C	D	E	F	G	H	I	J
1
2	1				16	6	4	6
3	2				8	2	3	3
4	3				19	1	6	6	6
5	4				15	1	2	3	9
6	5				10	1	2	3	4
7	6				0
8	6				0
9	7				0
Sheet1

If the cursor is in E3,... and so on for the others

cformat.xls
	A	B	C	D	E	F	G	H	I	J
1
2	1				16	6	4	6
3	2				8	2	3	3
4	3				19	1	6	6	6
5	4				15	1	2	3	9
6	5				10	1	2	3	4
7	6				0
8	6				0
9	7				0
10	6				0
11	9
12	3
13	11
Sheet1

 

[Von Pookie]

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

 

[Arnolf]

Very quick reply Kristy.
Thank you so much for your concern.
Hope somebody else find why that happen.
rgds,
Arnolf