need button that will highlight cells with 26 or 48 in them

I'm trying to get a code that will check a few columns to see if the cells contain 26 or 48 in them and if they do then highlight the cells. I can do this with conditional format but the conditional formatting gets erased each time i put new data in the cells (i have another code that ports in the values). Here is an example of what I want to see but i cant figure out how to make the code to check it. I'm only looking at columns L, V and AF from row 5 to 28. all the other data does not need to be checked.

Hi Kevinu,

How to you put new data in?
You could just past value so you conditional formating is not effected?

Give it a try it might just be that easy

Grtz

Added command button to worksheet and attached the following code:

Please Login or Register  to view this content.

i use coding like this on 3 seperate sheets to get data on this sheet

Please Login or Register  to view this content.

the conditional formatting on the other sheets gets copied to this sheet, which erases anything on this sheet.

I found this:

Sheet1.Cells(7, 4).Copy
Sheet1.Cells(7, 10).PasteSpecial Paste:=xlPasteValues

and that does the trick

i tried paste and it has other weird side affects. The code above worked great. I forgot to mention, is there now a way to check if any of the cells are colored then it stops or exits the code. I want to put this code in front of another one that exports all this data to yet another sheet so it will stop if there are any cells with 26 or 48 in them.

Bernie, I think your suggestion would work well too... have a helper cell and if its value is positive then the code exits. Ill try that.

If it's automated (macro), why check to see if any cells are colored rather than just checking to see if those cells have a 26 or 48 in them?

I want the user to see where they are so they can correct the data then rerun the export code. So it doesnt need to check for color if checking for values is easier, it just also needs to inform the user where the errors are.

tried to use this but doesnt work

Please Login or Register  to view this content.

unfortunately it doesnt recognize the interior color. Did some googling and apparently its almost impossible to detect a conditional formatted cells color. I got it to work with a helper cell but would love to have it detect conditional formatted color


used this

Please Login or Register  to view this content.

and this in helper cells

Formula:

Please Login or Register  to view this content.

Rather than using Conditional Formatting to color the cells per my original code (post #4), you could instead use normal cell formatting. This would allow you to check the cell's interior color, etc.

Please Login or Register  to view this content.