Macro Button to highlight specific text and change color If another column is empty

Morning all,

I hope you can help me out?

I have attached a copy of the doc With my current code. At the moment I have only been able to get to step one of the rules I'm trying to create.


I'm trying to create a Macro button that has the following rules.



When material in Column B with the following abbreviations is highlighted.



abbreviations are:- SHS, PFC, CHS, UC, UB, RHS, RSC, RSA, Key Steel, L1770, AWG, Bar, ACME, All Thread and Wear Strip.



If length in column H is present then text for both columns is highlighted in green.



If length is not present in Column H then material text is highlighted in red.


Any help would be greatly appreciated!


Cheers,

Hi Dave,

Thanks for your time!

What you have provided is great.

But the reason for having the button is due to the bill of material normally being a large file with hundreds of parts. So a simple button would just be a 1 click operation, and would highlight the materials with the abbreviations and any missing values.

The abbreviations are certain materials that are required to be cut lengths. And the manufacturing department are looking for a system to check the bills of material to see if any lengths have been missed.

If there is away of combining what you have provided into a button click macro then it would be perfect!

Thanks again,

Nick H

Try this attachment

Hi Mohadin,

Thanks for looking into this for me.

When I use the code you have provided I'm getting an error message. Regarding End Sub?

Cheers,

Nick H

Hi Dave,

Yes that is exactly what I need.

I would like it to select all rows that have the abbreviations.

Cheers,

Nick H

Originally Posted by mohadin

Try this attachment

I have tried it and It worked fine.

The issue I'm having now when I import it into a another document It wont work.

The Document I provided was edited to just show only a few fields.

I have attached a copy of a full bill of material.

Oh Doh!

I inputted the wrong Row numbers!

You can tell this is all new to me.

All working fine now!

Much appreciated!

Nick H

I hope I did understand

Originally Posted by mohadin

I hope I did understand

Almost Mohadin,

Please see attached! Just need to change it to row 8.

The last bit now is that it highlights all of row 8 in red.

Is there a way of changing the other cells that are not part of the pattern in a different color Like Blue.

So only the cells with the pattern are red or green dependent on length being present.

Cheers,

Nick H.

Ok
Let me know if this what you want

Originally Posted by mohadin

Ok
Let me know if this what you want

Almost,

just need to be able to use the button command so It only highlights the abbreviations and missing lengths.

As currently the whole column is red which makes it hard to read which one is missing a length.

Cheers,

Nick H.

Hi
Perhaps something like

Please Login or Register  to view this content.

Originally Posted by NeedForExcel

Hi,

Please try this out

Please Login or Register  to view this content.

Works great as a macro! Thanks for your help.

Originally Posted by mohadin

Hi
Perhaps something like

Please Login or Register  to view this content.

Works great as a button command!

Great help from you both! Rep added and a BIG thanks to you both