Conditional formatting in one formula

Hi Yasser,

This bit of code as an EVENT macro on the appropriate sheet (not a module) will do the trick.

You may have to modify the Interior Index number - I think the color pallet has changed. I am using XL2013, ng get some strange colours. I used the numbers you indicated in your attachment.

Please Login or Register  to view this content.

I hope this helps, please let me know!

Regards,

David


- Please click on the *Add Reputation button at the bottom of helpful responses.

Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

If several people have responded, when you reply please make it clear WHO you are responding
to by mentioning their name.

You're welcome Yasser,

I tried your method, but couldn't get it to work the way you described. It was B5 which did not change, and I had to double click. Is that what you meant?

Sorry, but I don't know how to resolve this, however, you should add one extra line to your function.

Case Else: rngToColor.Interior.ColorIndex = 0 - just to reset the colours when there is an error.

HOWEVER, I discovered one little trick! After I entered the =ColorRange($B$2,$B$5:$E$10) into the Conditional Formatting, It automatically set up =$B$5:$E$10 in the Applies to box.

I changed this to =$A$5,$B$5:$E$10 and it worked!

Yasser - Test.xlsm

I hope this helps, please let me know!

Regards,

David


- Please click on the *Add Reputation button at the bottom of helpful responses.

Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

If several people have responded, when you reply please make it clear WHO you are responding
to by mentioning their name.

Didn't get that for me, but there was some erratic behavior.

I think this is being caused by the UDF not working well with Conditional Formatting.

The solution I gave you originally does not use CF, but just shades the cells. Frankly, I prefer this!

Someone more skilled than me may have to contribute!

I hope this helps, please let me know!

Regards,

David


- Please click on the *Add Reputation button at the bottom of helpful responses.

Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

If several people have responded, when you reply please make it clear WHO you are responding
to by mentioning their name.

Just go to this site :https://msdn.microsoft.com/en-us/lib...ffice.12).aspx

Regards,

David