Color Filtered Data

[bassemjohn1]

Hello All

I need to filter column M for X and highlight green the filtered data in column K, i already highlighted the filtered data in column L successfully but when i try to do the same for column K, it doesn't work and it highlight wrong cells

1542001 - 2364001 Recon June- final 5.XLSX
	K	L	M
1	Amount in local currency	Recon.	Match
2	-20,269.20	20269.2-1	x
3	-21,500.00	21500-1	x
4	-1,042.65	1042.65-1	x
5	-111,198.38	111198.38-1
6	-17,000.00	17000-1	x
7	-2,146.20	2146.2-1	x
8	-2,100.00	2100-1	x
9	-10,821.00	10821-1	x
10	-103,949.21	103949.21-1
11	-197,321.76	197321.76-1
12	-15,916.12	15916.12-1
13	-376,754.43	376754.43-1	x
14	-391.05	391.05-1	x
15	-146,771.46	146771.46-1
16	-105,857.06	105857.06-1
17	-4,595.12	4595.12-1	x
18	-1,051.67	1051.67-1	x
19	1,221.22	1221.22-1
20	-50.00	50-1	x
21	-1,051.67	1051.67-2	x
22	-3,339.00	3339-1
23	-2,076.92	2076.92-1
24	-207.38	207.38-1
25	-31,442.10	31442.1-1	x
26	-7,905.45	7905.45-1	x
27	-9,414.00	9414-1	x
28	-50.00	50-2	x
29	-8,480.00	8480-1	x
30	-8,221.26	8221.26-1	x
31	-1,825.65	1825.65-1	x
32	-264.43	264.43-1
AEA - AEJ

Cell Formulas
Range	Formula
L2:L32	L2	=IF(K2<0,-K2&"-"&COUNTIF(K$2:K2,K2),K2&"-"&COUNTIF(K$2:K2,K2))
M2:M32	M2	=IF(COUNTIF($L$2:$L$292,L2)=2,"x","")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
K:K	Cell Value	duplicates	text	NO
L:L	Cell Value	duplicates	text	NO

My Code :

 Columns("L:M").Insert
 Range("L1").Value = "Recon."
 Range("M1").Value = "Match"
 Range("L2").Formula = "=IF(RC[-1]<0,-RC[-1]&""-""&COUNTIF(R2C[-1]:RC[-1],RC[-1]),RC[-1]&""-""&COUNTIF(R2C[-1]:RC[-1],RC[-1]))"
Range("M2").Formula = "=IF(COUNTIF(R2C12:R292C12,RC[-1])=2,""x"","""")"
Range("L2").Copy Range("L3:L" & Cells(Rows.Count, 4).End(xlUp).Row)
Range("M2").Copy Range("M3:M" & Cells(Rows.Count, 4).End(xlUp).Row)
Range("L1").Select

With Worksheets("Sheet1").Range("A1")
 .AutoFilter Field:=12, Criteria1:="-0"
 
   Range("L2", Range("L2").End(xlDown)).SpecialCells(xlCellTypeVisible).ClearContents
If ActiveSheet.AutoFilterMode Then ActiveSheet.ShowAllData
With Worksheets("Sheet1").Range("A1")
.AutoFilter Field:=13, Criteria1:="x"
    Range("L2").Select
    Range(Selection, Selection.End(xlDown)).Select
    
    ws.Range("L:L").FormatConditions.AddUniqueValues
    Set HighlightDuplicates = ws.Range("L:L").FormatConditions(1)
    
     With HighlightDuplicates
        .SetFirstPriority
        .DupeUnique = xlDuplicate
        .Font.Color = vbBlack
        .Interior.Color = 5287936
        .StopIfTrue = False
        End With


    Range("K2").Select
    Range(Selection, Selection.End(xlDown)).Select
    
    ws.Range("K:K").FormatConditions.AddUniqueValues
    Set HighlightDuplicates = ws.Range("K:K").FormatConditions(1)
    
     With HighlightDuplicates
        .SetFirstPriority
        .DupeUnique = xlDuplicate
        .Font.Color = vbBlack
        .Interior.Color = 5287936
        .StopIfTrue = False
        End With

 

[gnaga]

Why don't you use column M value as condition for formating Column K and L? It is simple isn't?

 

[bassemjohn1]

Why don't you use column M value as condition for formating Column K and L? It is simple isn't?

would you please help me writing a macro for it

 

[bassemjohn1]

would you please help me writing a macro for it

i tired this code

'Create variables to hold the number of rows for the tabular data
Dim RRow As Long, N As Long
'Capture the number of rows within the tabular data range
RRow = ActiveSheet.UsedRange.Rows.Count
'Iterate through all the rows in the tabular data range
For N = 1 To RRow
    'Use a Select Case statement to evaluate the formatting based on column 2
    Select Case ActiveSheet.Cells(N, 13).Value
        'Turn the interior color to blue
        Case "x"
        ActiveSheet.Cells(N, 11).Interior.Color = vbGreen
        'Turn the interior color to red
        Case "x"
        ActiveSheet.Cells(N, 12).Interior.Color = vbGreen

    End Select
Next N

it only worked on column K but not L

1542001 - 2364001 Recon June- final 5.XLSX
	K	L	M
1	Amount in local currency	Recon.	Match
2	-20,269.20	20269.2-1	x
3	-21,500.00	21500-1	x
4	-1,042.65	1042.65-1	x
5	-111,198.38	111198.38-1
6	-17,000.00	17000-1	x
7	-2,146.20	2146.2-1	x
8	-2,100.00	2100-1	x
9	-10,821.00	10821-1	x
10	-103,949.21	103949.21-1
11	-197,321.76	197321.76-1
12	-15,916.12	15916.12-1
13	-376,754.43	376754.43-1	x
14	-391.05	391.05-1	x
15	-146,771.46	146771.46-1
16	-105,857.06	105857.06-1
17	-4,595.12	4595.12-1	x
18	-1,051.67	1051.67-1	x
19	1,221.22	1221.22-1
20	-50.00	50-1	x
21	-1,051.67	1051.67-2	x
22	-3,339.00	3339-1
23	-2,076.92	2076.92-1
24	-207.38	207.38-1
25	-31,442.10	31442.1-1	x
26	-7,905.45	7905.45-1	x
27	-9,414.00	9414-1	x
AEA - AEJ

Cell Formulas
Range	Formula
L2:L27	L2	=IF(K2<0,-K2&"-"&COUNTIF(K$2:K2,K2),K2&"-"&COUNTIF(K$2:K2,K2))
M2:M27	M2	=IF(COUNTIF($L$2:$L$292,L2)=2,"x","")

 

[bassemjohn1]

i tired this code

'Create variables to hold the number of rows for the tabular data
Dim RRow As Long, N As Long
'Capture the number of rows within the tabular data range
RRow = ActiveSheet.UsedRange.Rows.Count
'Iterate through all the rows in the tabular data range
For N = 1 To RRow
    'Use a Select Case statement to evaluate the formatting based on column 2
    Select Case ActiveSheet.Cells(N, 13).Value
        'Turn the interior color to blue
        Case "x"
        ActiveSheet.Cells(N, 11).Interior.Color = vbGreen
        'Turn the interior color to red
        Case "x"
        ActiveSheet.Cells(N, 12).Interior.Color = vbGreen

    End Select
Next N

it only worked on column K but not L

1542001 - 2364001 Recon June- final 5.XLSX
	K	L	M
1	Amount in local currency	Recon.	Match
2	-20,269.20	20269.2-1	x
3	-21,500.00	21500-1	x
4	-1,042.65	1042.65-1	x
5	-111,198.38	111198.38-1
6	-17,000.00	17000-1	x
7	-2,146.20	2146.2-1	x
8	-2,100.00	2100-1	x
9	-10,821.00	10821-1	x
10	-103,949.21	103949.21-1
11	-197,321.76	197321.76-1
12	-15,916.12	15916.12-1
13	-376,754.43	376754.43-1	x
14	-391.05	391.05-1	x
15	-146,771.46	146771.46-1
16	-105,857.06	105857.06-1
17	-4,595.12	4595.12-1	x
18	-1,051.67	1051.67-1	x
19	1,221.22	1221.22-1
20	-50.00	50-1	x
21	-1,051.67	1051.67-2	x
22	-3,339.00	3339-1
23	-2,076.92	2076.92-1
24	-207.38	207.38-1
25	-31,442.10	31442.1-1	x
26	-7,905.45	7905.45-1	x
27	-9,414.00	9414-1	x
AEA - AEJ

Cell Formulas
Range	Formula
L2:L27	L2	=IF(K2<0,-K2&"-"&COUNTIF(K$2:K2,K2),K2&"-"&COUNTIF(K$2:K2,K2))
M2:M27	M2	=IF(COUNTIF($L$2:$L$292,L2)=2,"x","")

i tried double cond. for. and it worked, Thank you so much Gnaga !!

 

[gnaga]

My proposal was this without Macro

Book5.xlsx
	K	L	M
1	Amount in local currency	Recon.	Match
2	-20269.2	20269.2-1
3	-21500	21500-1
4	-1042.65	1042.65-1
5	-111198	111198.38-1
6	-17000	17000-1
7	-2146.2	2146.2-1
8	-2100	2100-1
9	-10821	10821-1
10	-103949	103949.21-1
11	-197322	197321.76-1
12	-15916.1	15916.12-1
13	-376754	376754.43-1
14	-391.05	391.05-1
15	-146771	146771.46-1
16	-105857	105857.06-1
17	-4595.12	4595.12-1
18	-1051.67	1051.67-1	x
19	1221.22	1221.22-1
20	-50	50-1
21	-1051.67	1051.67-2	x
22	-3339	3339-1
23	-2076.92	2076.92-1
24	-207.38	207.38-1
25	-31442.1	31442.1-1
26	-7905.45	7905.45-1
27	-9414	9414-1
Sheet2

Cell Formulas
Range	Formula
L2:L27	L2	=IF(K2<0,-K2&"-"&COUNTIF(K$2:K2,K2),K2&"-"&COUNTIF(K$2:K2,K2))
M2:M27	M2	=IF(COUNTIF($K$2:$K$27,K2)=2,"x","")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
K2:L27	Expression	=($M2="X")	text	NO