Conditional Formatting Issues

[jcolesRA725]

Hi,

I'm fairly new to conditional formatting. I know some of the basics, but I rarely need to use Excel beyond creating simple tables, but I have recently put together a table that I use to track my workload.

Due to the nature of my work, I have to work on many different projects, each with their own tasks. I have set up a table in my spreadsheet which has the name of the project, the project reference number, the tasks to be completed, and a log of how many hours I've been spending on each task on a calendar type set.

I have set up conditional formatting to show the current day, when I'm away, etc. but I have also shown all the days of the week, so the last week ends with the 1st and 2nd of September. I have set up conditional formatting to show these two days as dark grey. Everything until this point is working fine.

I have set up a blank group of cells which are to the side of my table and a macro, so when I get a new project come in, I can push a button and these cells will be copied and pasted at the top of the table. Everything works perfectly after, except the conditional formatting for the cells AM7:AN9 hasn't carried over properly. If you look at the attachment, you can see the issue I am having.

I think it might be because there are three rows being inserted at row 7, which then changes the formula in the top image =P$34="-" (next time I press the button it will be =P$37="-"), but the formula in the second image always stays the same =BN$25="-". So for the formula to work, I need it to read as =BN$34="-" and for it to change automatically whenever new rows are added.

Is there a way to do this?

Apologies if this doesn't make much sense. I will answer questions to clarify anything.

Thanks

[AliGW]

Hello and welcome! Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[jcolesRA725]

Thanks for replying.

I have attached the worksheet with all the sensitive info deleted. The main cells in question are P to AN.

Thanks