Conditional Formatting for Constantly Changing Spreadsheet

[vlina]

Hello,

I have a spreadsheet (attached) where I keep track of papers that are submitted to our journal. Papers come in, go out for peer review (to 2-4 reviewers), then our editors make a final decision.

I need to highlight several things:

- If more than 50% of the reviews are in (ex. 2 of 3 or 3 of 4), highlight the number of reviews in red & bold.

- If the paper is with us (age) for more than 36 days, make age red, if it's over 45 days, make it red & bold

- etc.

(PS. There is an event change in the code right now, such that whenever I enter a new date in "Next day to remind reviewer", it sorts by the date the next chase is due.)

I've been using conditional formatting, however every day, new papers come in and others are deleted (decision is sent, file is closed). Because rows are constantly deleted and added, the cell references in the conditional formatting gets messed up, things that are supposed to be highlighted aren't, and I have to rewrite the conditions every couple of weeks.

So, I'd like to write this out in code instead. I've tried to look this up for a while online, but can't figure it out. Could someone please lead me in the right direction?

Thank you,

[Bob Phillips]

Delete all of your existing rules, and

1 - change the formula in N2 to =IF(ISBLANK(F2),"",F2/G2) and copy down

2 - select the CF cells and add a rule of ,=$N2>0.5, format as required, then add the rest similalrly

[vlina]

Thank you very much for your help.