SOLVED

How to highlight the cells using a formula with conditional formatting or else

Copper Contributor
Hi Team,

I have a specific worksheet with bunch of unique lets say serial numbers. Each serial number has it's own description.
The serial numbers are in Column A
The description is in Column B.

1) I have added Column C with a Vlookup to check the serial numbers that I am getting from another worksheet on a weekly basis and copied them into Sheet1.
The reason is when I have the serial numbers that are matching from Sheet1 with the ones in sheet "Test" those serials need to be highlighted in Green but the problem is that I need the highlighted area to be with all the information I have for this serial number. For example the first serial in my list is 75E395X
I need all rows from Row 2 to Row 16 to be highlighted in Green if this serial is found with the Vlookup formula.

Another condition I need is probably more complicated and I really don't know how to start.

2) Lets take the second serial number 75E3A4M. If for this serial number with matching values from row B at least 3 times Test1 at least 3 times Test 2 and at least 3 times Test3 again to highlight the area for this serial number in Green which is Row 17 to row 31 included. If this can be made we can skip point 1 as it will be the same.

and the last condition I need is:

3) If for a serial number it doesn't see Test3 in Column B but sees at least 3 Times Test1 and at least 3 Times Test 2 to highlight the area in yellow. Lets take Serial: 75E3A4Y
for example and in this case the rows to highlight are Row 32 to Row 41 included.

Does that make sense? Thank you in advance! I am making this by hand one be one and it takes me like 3 hours to complete this worksheet and the serial numbers will get more and more and I want to know if there is a chance to shorten this with formulas!
 
7 Replies

@Steiny88 

Hi, I've added the conditional formatting you wanted. I hope it fits like this. However, I need the auxiliary column D. You should not delete it. But you can move it or hide it. The formula is in cell D2.

You can find the result in the attached Excel sheet.

@dscheikey 
Hi,

When I downloaded the file on my iPhone it opens up and I see the formula but when I downloaded the file on my laptop it spills an error in the formula (the error is inside the screenshot attached). I looked up the error and it gives me that specific office version does not support all functions. I am with Office 365 Enterprise (also screenshot attached). I believe that it is working but cannot check it. Can you paste the formula from D2 here so I can try and copy paste it in the excel on my laptop.

 

Thank you very much!

best response confirmed by Steiny88 (Copper Contributor)
Solution

@Steiny88 

If you get the formula displayed like this, then your Excel version does not yet support SCAN(). Excel for the web already does, so it also works on the i-Phone.

=SCAN(,INDIRECT("A2:A"&LOOKUP(2,1/(A:A<>""),ROW(A:A))),LAMBDA(a,b,IF(OR(b="",b="Subtotal"),a,b)))

I have now modified it so that it should also work on older versions of Excel. If you have more than 1000 entries, you have to extend the formulas in the sheet as well as in the conditional formatting.

@dscheikey 

Hi again :)
I've reviewed the last excel you've attached and changed the values in B66,67 and 68 to Test3 in order to test if it will make it green as we have at least 3 times Test1, Test2 and Test3 but it's not working.
Can you please check the attached worksheet. I didn't change anything else. I don't know if formula 3) is compromised from Formula 2) if it finds the number inside sheet1.
Thank you!

@Steiny88 

I think it is just a small error. Please change the second conditional formatting at the very back from "2".

=AND(COUNTIFS($D$2:$D$1000,$D2,$B$2:$B$1000,"Test1")>2,COUNTIFS($D$2:$D$1000,$D2,$B$2:$B$1000,"Test2")>2,COUNTIFS($D$2:$D$1000,$D2,$B$2:$B$1000,"Test3")>2)

Sorry for a small careless error.

THANK YOU VERY MUCH!!!
I cannot thank you enough....

 

how can I promote you or send some kind of thanks in this forum? I hit the Best Response button but couldn't find anything else?

:)

@Steiny88 

You can click the Like button (the thumb up icon) at the bottom of @dscheikey's reply/replies.

1 best response

Accepted Solutions
best response confirmed by Steiny88 (Copper Contributor)
Solution

@Steiny88 

If you get the formula displayed like this, then your Excel version does not yet support SCAN(). Excel for the web already does, so it also works on the i-Phone.

=SCAN(,INDIRECT("A2:A"&LOOKUP(2,1/(A:A<>""),ROW(A:A))),LAMBDA(a,b,IF(OR(b="",b="Subtotal"),a,b)))

I have now modified it so that it should also work on older versions of Excel. If you have more than 1000 entries, you have to extend the formulas in the sheet as well as in the conditional formatting.

View solution in original post