Hi, I'm trying to set up some conditional formatting based on what the colour of 4 other cells are in the same row, I'm trying to get it so that if any of them are a specific colour then it will change the colour of the cell I'm doing the formatting on. I'm using Office 2016.

For example, I want A2 to turn red if B2, C2, D2 or E2 are red, if they are all green I want A2 to stay the same colour, if only one of them is red I want it to turn red, I hope that makes sense. I've got conditional formatting on the B:E columns that turn them green if the dates are within this month or last month, if they aren't then they don't change colour. But the cells themselves are coloured red by default.

I've tried one thing I found online but it hasn't worked. I tried to post a link to the method but it won't allow me as this is my first post.

Thanks in advance for any help provided