Formula to referance multiple cells on another sheet excel 365

[Matt Young]

Hi guys

I am trying to do a backup spreadsheet for all the devices at work (each device has 32 "nodes" in them so its one sheet per device) I have got everything working on each sheet (expiry dates, filters macros etc) but I am trying to set up one front "Status"page that shows you if work needs doing on a device (kind of a quick glance page). my question is

does anyone know how to make one cell turn red on one page if ANY (more than one) cells on another page are red. I have tried conditional formating but it says that I can't use a range of cells only one referance cell.

Thanks for the help

Matt

 

[Rijnsent]

Hi Matt,
what you could do is per sheet have one cell with a formula (a UserDefinedFunction in VBA) that counts the nr of red cells in that sheet and use that as your reference. See e.g. this for a UDF: https://answers.microsoft.com/en-us...ll-color/7769452b-6119-4a02-8799-64b26f137c94
Cheers,
Koen