0

Within a workbook I have one worksheet ('Attendance') containing cells with comments where the fill tool has also been used to indicate the comment category (the cells are yellow for uncertain, as shown below, green for certain, red for uncertain etc).

'Attendance' sheet showing example of comment:

enter image description here

I have another sheet ('Comments') which lists any cells with comments from 'Attendance' (IE the active sheet) and also a cell reference in column B referring to the cell in which the comment appears. This was generated using a Macro I found on here.

'Comments' sheet showing columns for sheet name, cell reference, cell value, comment, respectively

enter image description here

What I'd like to do is to run a macro that copies the cell colour of the cell in 'Attendance' referenced in column B of 'Comments'. I'd also like to know how to apply this to the whole of column B of 'Comments', as shown in the attached image below

'Comments' showing intended effect of colouration based on cell reference in other sheet

enter image description here

Any help would be greatly appreciated as I couldn't find another thread that encapsulated the question of copying cell colour based on cell reference in another sheet. I think a solution might be based on using Indirect but I'm not sure about since I'm new to VBA.

0

This is a pretty simple VBA code, but does the job:

Sub CopyColors()
    For i = 1 To Cells(Rows.Count, 2).End(xlUp).Row
        Sheet2.Range("B" & i).Interior.Color = Sheet1.Range(Sheet2.Range("B" & i).Text).Interior.Color
    Next i
End Sub

Cells(Rows.Count, 2).End(xlUp).Row finds the last row in column B that contains data, the rest is pretty straightforward. Of course Sheet2 refers to sheet "Comments", Sheet1 to "Attendance". You can also use Range("Comments!B" & i) instead of Sheet2.Range("B" & i), then inserting a new sheet has no effect on the references (just changing the sheet names).

Maybe it can be done with conditional formatting (without a macro), but I think that is really painful especially if you have lots of possible colours. I am not even sure if it is possible.

EDIT: You should paste the code to the workbook's module in VB editor, like here

  • This didn't work with me, even when I changed the sheet names it still wanted to debug this second line of code: Sheet2.Range("B" & i).Interior.Color = Sheet1.Range(Sheet2.Range("B" & i).Text).Interior.Color Does selecting the range with Sheet1.Range(Sheet2.Range definitely refer to the contents of the cells? Thank you very much for swift response, by the way – Jonnym93 Jun 3 at 17:23
  • Well, Sheet2.Range("B" & i).Text should be the content of the cell, in your case something like $AG$70. So this cell value is used to create a range. Anyway, I have created a new Excel file and copied the VBA code I have submitted above, and it still works. Where did you paste the code? Didn't you accidentally paste it in the Sheet's module, not in the workbooks? I will make an edit to the answer, because I cannot insert a screenshot here. – household UFO Jun 4 at 16:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.