VBA Multiple Conditional Formatting

[slohman]

I have tried macro recorder with no luck I can get it to work if I use excel conditional formatting but I need this to be a VBA macro

All the conditional formatting help I see on all the sites don't seem to help with what I want.

I need to be able run a macro anytime even on open or close or save.

=if(and(Column A has the word "Dial" or "Special" or "Install" and part of the word so maybe using a wildcard) and (Column F has the word "Booked") and (Column J has a date in it I need the Columns from Row A:M to turn Blue.

I then have about 30 more conditions to add to the macro.Works Project.xlsm

[The Flying Scot]

Not sure if you could adpt this code for what you want it to do.

Please Login or Register  to view this content.

[slohman]

Thanks but I tried something like that but I need it to colour all the Columns from A4:M???

I also need 3 cases on the same line like an if and statement

[Philb1]

Hi Slohman
Have a look at this. I've commented out your worksheet change code, but only to stop it working when I was testing my code. Hopefully it'll give you something to work on & work with. I think it's doing what you need. I've attached the workbook
Cheers
Phil

Please Login or Register  to view this content.

[slohman]

Thank you so much for that but I think I maybe I have not explained myself properly I need it to change the row if the Word Dial and Word Booked and a Date is found all of these not just any of these. Also for each row I need to have a different colour.

Light Blue for Dial Row
Light Red for Install
etc

[Philb1]

Hi slohman.. Try this. The reason it was colouring when it shouldn't was the hidden columns I didn't count. Fixed it now

Please Login or Register  to view this content.

[slohman]

Thank you Thank you Thank you

You are brilliant that has got to be the best macro ever.

Do you know how I can fix this little problem.

[Philb1]

Sorry.. I've never tried sending emails from Excel. I'd have to learn how to do it first

[slohman]

Thanks for everything. If you learn how to do it can you post me

[slohman]

Works Project 2014 28032014.xlsm
I have just tried this on my worksheet but with no luck after the initial rows.

I have blank rows i need it to continue down each row until end.

I also noticed that if the word booked in "F" or the date in "J" is not entered the row is still coloured I need it to colour if all 3 columns are filled "A" "F" and "J".

[Philb1]

I looked at your works project book & it's working as it should, including down to row 34. Did you clear all the row colours between your tests?

[slohman]

If you mean did I use your Sub ResetCondFormatting yes I did all the colours disappeared but I would never clear the contents of all the rows it is a worksheet that I add to everyday.

I then did Sub CondFormatMultiColour and it goes done to row 22 and stops the colouring.

[slohman]

Thanks Phil I have figured it out.

Thanks again for all your help. I might need you again for your expertise as I need to continue on with the rest of the colouring.

[Philb1]

This will fix the not going to the bottom of the sheet

Please Login or Register  to view this content.

[slohman]

Just wondering if you have any thoughts on the next macro I need or if I can add to the one you wrote CondFormatMultiColour I have set up more conditional formatting it is very similar it is =AND($J2>0,$A2="Dial before you Dig",P$1>=$J2,P$1<=$K2) would love to know how to right this into a vba. If you look at the original attachment I am trying to set up a gantt chart.

[Philb1]

It looks like you're going well with the conditional formatting + it would be a real mission, trying to set up what you've got set up, with a macro. It's easier to fix any errors in the conditional formatting on the sheet, than in code.

[slohman]

Is there a way that I can delete all formatting from say Column P4 to BS700 like a reset formatting with your macro and then set formatting again copying all formatting from say cell P3 that would have all the conditional formatting attached. Maybe I could some how you format painter??

[Philb1]

I have no idea if that's possible, but my first thought is it's not. CF works from the conditions you've set on the sheet. I would think the only way to do what you're thinking of is to set the whole lot up in a macro. But don't take my word for it lol.
The reason I put the clear formatting on the sheet is I was too lazy to keep clearing the formatting manually when I was playing with it. I didn't expect you to like it

[slohman]

Ok so we are back to my best option being a macro wondering if we could write a new one base on your original that includes =AND($J2>0,$A2="Dial before you Dig",P$1>=$J2,P$1<=$K2) I have taken this statement straight from Conditional formatting. I dont know how to write this into a macro to colour certain cells if this is correct. Range being P:BS down. The cells colours would be exactly the same as the previous macro.
Thanks

[slohman]

Just wondering if someone can help with this?

[Philb1]

Hi Slohman.. I don't understand why you want it in a macro. If it's working as standard CF, why the extra work?

[slohman]

The main reason I thought a macro would be better is I move all the rows around depending on job scheduling and it changes all the conditions and I also have to enter rows in and then that adds extra CF. So I thought each day end of or start of I would reset formatting with a macro and then run macro to check again and put CF back in.

[Philb1]

I've got a lot on my plate right now & haven't got the time to do a what appears to be a mission. It might be easier if you thought of another way to run the spreadsheet, as moving rows around & changing conditions with it sounds like it's over complicated. But that's only my opinion.
You've got the macro I did with the conditional formatting, you could copy it into another module, rename it & then adapt it to suit whatever you're doing. It's a learning curve I know, but it's the best way to get exactly what you want. It's the route I took. I had to as my project was an ever changing mission & on top of that, until a couple of years ago,I hadn't done anything with spreadsheets. It was just a screen full of rectangles.
Remember, Google/Bing are your friends

[slohman]

Thanks for that I will do