Hi everyone, I'm new on this forum, have searched for an answer to this problem for a while with no success.

Basically I am trying to design a spreadsheet for work with limited knowledge of VBA. On a particular sheet there are certain rows shaded in grey (colour index = 15). I am using some data from this sheet (sheet1) on another sheet (sheet2) and need to do this using arrays as data on sheet1 is frequently reordered / deleted / inserted (entire rows) and this breaks my system as excel updates the links on sheet 2 and so reordering does not occur. Also I need to advantage of arrays updating real-time.

What I am trying to achieve is getting the rows shaded grey on sheet1 to also become shaded grey on sheet2. I can do this using conditional formatting. Currently my workaround is to insert a marker such as "." in a hidden column in the rows that have gray shading. This is then read by an array on sheet2 and conditional formatting applied. This works perfectly, but I am wary that if a new grey row is created, or someone doesn't copy the "." or accidentally deletes it, then the system will break (yes, I am paranoid). My ideal situation is therefore to read the cell colour of the cells.

I've written a function to do this, which works:

Function Cellcolour(iRow As Integer, iCol As Integer) As Variant

Cellcolour = Sheets("Sheet1").Cells(iRow, iCol).Interior.ColorIndex

End Function
This works perfectly when used in excel on sheet2, reading off the corresponding cells in sheet1:

Formula: copy to clipboard 
 =Cellcolour(ROW(A1),COLUMN(A1))


Sheet2 is protected and read only so I'm not worried about the hidden column in which this is being deleted / messed with.

The problem is when I try to use this formula in an array over e.g. Sheet2 F1:F199 it returns a #value. And I just can't get it to work

Formula: copy to clipboard 
 {=Cellcolour(ROW(A1:A199),COLUMN(A1:A199))}



I have created a workaround using VBA and putting this code on Sheet2:

Private Sub Worksheet_Activate()

Dim intR As Integer

For intR = 1 To 199
    Sheets("Sheet2").Cells(intR, 6).Value = Sheets("Sheet1").Cells(intR, 1).Interior.ColorIndex
Next intR

End Sub

This does exactly what I want but I need to use VBA to make any changes, which may be difficult for someone else in the future who doesn't understand VBA, and this requires me to change to Sheet2 inorder to update the values. The only potential problem with this is that someone may print off the workbook (including sheet2) without changing to it after having made changes to sheet1. And therefore things may mess up somewhat. Hence I would prefer to use the array method using the function.

Any help would be much appreciated! Thanks