If Index/Match value is less than not working

[KDS14589]

I have a list of numbers between 0 to 100 in column L. And a list of items in column O. What I'm trying to do is if that single item has a 'lightness' number less or equal to 50 then the font in certain cells change color.
I need another set of eyes because I cannot get it to work or another approach.

Dim Item As Range    'single Item
Dim Lightness As Range   'Lightness list
Set Lightness = ShGE03.Range("L5", ShGE03.Range("L" & ShGE03.Rows.Count).End(xlUp))
Dim List As Range   'List of items
Set List = ShGE03.Range("O5", ShGE03.Range("O" & ShGE03.Rows.Count).End(xlUp))

Set Item = ShGE03.Range("A5")    'single item
If WorksheetFunction.Index(Lightness, WorksheetFunction.Match(Item, List, 0)).Value <= 50 Then
Range("A1:A4").Font.Color = vbWhite
Range("A37:A38").Font.Color = vbWhite
Range("A47:A48").Font.Color = vbWhite
Else
Range("A1:A4").Font.Color = vbBlack
Range("A37:A38").Font.Color = vbBlack
Range("A47:A48").Font.Color = vbBlack
End If
/CODE]

 

[James006]

Hi,

Have you already tested

If WorksheetFunction.Index(Lightness, WorksheetFunction.Match(Item, List, 0)) <= 50 Then

 

[MARK858]

Your code is running fine for me with the data set below A1:A4 on the Activesheet the font changes to black when I have G in A5 and changes to white when I have B in A5 (all I changed to test was ShGE03 to ActiveSheet so it was all on the same sheet and put in a debug.print line to check the value returned)

I can't tell if ShGE03 is the issue here as you haven't posted where you define the sheet.

Book1.xlsb
	A	B										L	M	N	O
1	R
2	S
3	T
4	U
5	G											45			A
6												43			B
7												12			C
8												25			D
9												47			E
10												36			F
11												79			G
12												14			H
13												12			I
14												3			J
15												16			K
16												7			L
17												14			M
18												56			N
Sheet3

Edit: now also tested with defining ShGE03 as a different sheet and it still changes the A1:A4 cells on the Activesheet

 

[KDS14589]

Your code is running fine for me with the data set below A1:A4 on the Activesheet the font changes to black when I have G in A5 and changes to white when I have B in A5 (all I changed to test was ShGE03 to ActiveSheet so it was all on the same sheet and put in a debug.print line to check the value returned)

I can't tell if ShGE03 is the issue here as you haven't posted where you define the sheet.

Book1.xlsb
	A	B										L	M	N	O
1	R
2	S
3	T
4	U
5	G											45			A
6												43			B
7												12			C
8												25			D
9												47			E
10												36			F
11												79			G
12												14			H
13												12			I
14												3			J
15												16			K
16												7			L
17												14			M
18												56			N
Sheet3

Edit: now also tested with defining ShGE03 as a different sheet and it still changes the A1:A4 cells on the Acti

Your code is running fine for me with the data set below A1:A4 on the Activesheet the font changes to black when I have G in A5 and changes to white when I have B in A5 (all I changed to test was ShGE03 to ActiveSheet so it was all on the same sheet and put in a debug.print line to check the value returned)

I can't tell if ShGE03 is the issue here as you haven't posted where you define the sheet.

Book1.xlsb
	A	B										L	M	N	O
1	R
2	S
3	T
4	U
5	G											45			A
6												43			B
7												12			C
8												25			D
9												47			E
10												36			F
11												79			G
12												14			H
13												12			I
14												3			J
15												16			K
16												7			L
17												14			M
18												56			N
Sheet3

Edit: now also tested with defining ShGE03 as a different sheet and it still changes the A1:A4 cells on the Activesheet

I changed my code to that ‘ActiveSheet’ and now the code works. Even though the ActiveSheet is the same as ShGE03, that is just the codename for that sheet. Before I did the renaming, I updated my version. So, I don’t know for sure if it was the ‘ActiveSheet’ or a corrupt excel, but either way it is working now.

Thanks for the help.

 

[MARK858]

More likely a corrupt file as no reason why it would error if the variable was defined correctly.

You're welcome and happy you have it sorted