Hi Guys, could someone help with a macro problem?

I have a number of sheets, lets say

Main
Sheet 2
Sheet 3
Sheet 4

I currently have set sheets 2,3 &4 with some conditional formatting telling
me when certian equipment is about due for test.

=H6<=Today() (indictaes red when out of date)
=H6<=Today()+30 (indictaes orange when within 30 days of exp date)
=H6=Today()+30 (indictaes green when in date)


What I would like is whatever the indictaion of the different pieces of
equipment on sheets 2,3 or4 is ("green" "orange" or "red") to flag up on the
main sheet.


i.e Shackle 1 on Sheet 2 indictaing "red" will flag up on the main sheet
under the "main" heading of Shackles "Red" Out of date "orange" due and
"green" In date.

I do hope this makes sense to someone because I am really baffled by code.

Thanks in advance.

Andy