Conditional Formatting

[Sarah T Cuttings]

Hi

I am looking for assistance with my below spreadsheet:

I have a conditional format in column I which highlights when the date is 7 days after the date in column H. I have the statuses of the claims in column G and want to link this to the formatting in column I to stop the red colour if the status is closed. Is there a way to do this?

Thanks
Sarah

 

[Peter_SSs]

In future it would help if you gave your sample data (including the current conditional formatting with XL2BB so that helpers can easily copy for testing.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Try something like this for your CF rule

=AND(I3=H3+7,G3<>"Closed")

 

[Sarah T Cuttings]

Hi Peter

My IT will not allow me to download the XL2BB unfortunately. Let me make myself clearer by putting all the formula's in as I did not help myself with the first post:

So, my cursor is in column I3 which shows you the formula for the date, I then have a conditional format which is turning the cell red. I want to turn off the conditional formatting if the text in column G states closed.

I hope this is clearer.

Thanks
Sarah

 

[Peter_SSs]

My IT will not allow me to download the XL2BB

Fair enough. In any future threads you start I suggest you state that in the opening post so we don't keep asking.

The conditional formatting formula for column I shown in your image seems to bear no resemblance to what you stated in post 1 about column I being 7 days after the date in column H

 

[Sarah T Cuttings]

Sorry, yes my first post was less than helpful. The second one is accurate.

Is this something you can help with?

Thanks
Sarah

 

[Peter_SSs]

Is this something you can help with?

Sure - add the G3 not equal to "Closed" condition just as I did in post 2.
=AND(I3>0,I3<=TODAY(),G3<>"Closed")

 

[Sarah T Cuttings]

Thanks Peter, all has worked well now. I just needed to add on the final part of the formula for it to work. I was having a trying day on Tuesday & Friday

 

[Peter_SSs]

You're welcome. Glad to help. Thanks for the follow-up.