Connecting Sheet 1 to Sheet 2
 

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

Connecting Sheet 1 to Sheet 2
 

Hi,

Add this function to a module :

Public Function getColor(cl As Range) As String
Application.Volatile
Dim temp
temp = cl.Interior.ColorIndex
Select Case temp
Case 3 'Red
getColor = "Expired"
Case 45 'Orange
getColor = "Expire in 30 days"
Case Else
getColor = ""
End Select
End Function
On your main sheet then, make a formula and point to the sheet 2,3 or 4
Reagards
Jean-Yves

"Sibbs" wrote in message
...
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