Cell Colour Format from front sheet

[nbqleebarnes]

Hi All,
I have had a look at the KB for conditional formatting and I am trying to modify it to change the colour of selected cells in sheet 2 from sheet one, ie, when sheet 1 cell A1 is red then sheet 2 Cell A1, A5, H3 would change to the same colour as the sheet 1 cell A1.
Could anyone point me in a direction of solving this issue.
Thanks
Lee

[GTO]

Hi Lee,

The below goes under the ThisWorkbook module. Needs possibly adjusted both for codename(s) and sheet name(s).

Hope this helps,

Mark


Private Sub Workbook_SheetActivate(ByVal Sh As Object)
If Sh.Name = "Sheet2" Then
If Sheet1.Cells(1, 1).Interior.ColorIndex = 3 Then
Sheet2.Range("A1, A5, H3").Interior.ColorIndex = 3
Else
Sheet2.Range("A1, A5, H3").Interior.ColorIndex = xlNone
End If
End If
End Sub

[Bob Phillips]

If you refer to cells on another sheet, name those cells and refer to that name in th CF formula.

[GTO]

@Lee:
Oops! I glossed right over your wanting to use conditional formatting.

@Bob:
Am I correct in this would then need a user-defined function, or is there a function included that would check interior? The only function I found was 'Cell()' but I have no idea what a negative color value would be.

Thank you so much,

Mark

[nbqleebarnes]

Thanks Mark, XLD,
It works fine although I was looking to make the specified cells on sheet 2 the same colour as Sheet 1 A1, no matter what the colour is, do I have to duplicate the code for each colour that there is?
Cheers
Lee

[Bob Phillips]

Oops, and I missed the colour part.

You are right, it would need a UDF. Such as



Function IsColoured(cell As Range, ci As Long) As Boolean
IsColoured = cell.Interior.ColorIndex = ci
End Function


but you would still have to use a named cell.

[GTO]

...when sheet 1 cell A1 is red then sheet 2 Cell A1, A5, H3 would change to the same colour...

Hi again Lee,

Well you did specify red, so that's the only color I checked for. In the case of matching colors, we can just change the test a bit:


Private Sub Workbook_SheetActivate(ByVal Sh As Object)
If Sh.Name = "Sheet2" Then
'// If A1 on Sheet1 is NOT empty of color...//
If Not Sheet1.Cells(1, 1).Interior.ColorIndex = xlNone Then
'// ...then we'll make it the same color as A1.//
Sheet2.Range("A1, A5, H3").Interior.ColorIndex = _
Sheet1.Cells(1, 1).Interior.ColorIndex
Else
'// Else, if A1 on Sheet1 is colorless, we'll empty the//
'// other cells of color too :-) //
Sheet2.Range("A1, A5, H3").Interior.ColorIndex = xlNone
End If
End If
End Sub

Given that you want to match color, I don't think conditional formatting will do.

@Bob:
Have to hit the rack, but if I attach a small wb later, would you mind showing me the name part? Not at naming ranges, got that; more at - when I read the help topic, it seemed to indicate that I'd also need to set a reference on the sheet containing the CF(s), wherein said reference points to the cell that one wants to actually check. Thank you much.

Mark

[nbqleebarnes]

Hi Mark,
I have tried the code you sent through, and I have got it to work for sheet 2 to any colour I need, then I added a couple more sheets and tried to duplicate the code to make the other sheets also change colour in the selected cells, but I can't get it to work, I have attached the WB, if you can please have a look at it for me I would be very thankful
Cheers
Lee

[nbqleebarnes]

Hi Again Mark,
Sorry, I should have made myself a bit more clear, I would like to make it so that the cells that are to be coloured, maybe different for each sheet.
Thanks
Lee

[GTO]

Hi Again Mark,
Sorry, I should have made myself a bit more clear, I would like to make it so that the cells that are to be coloured, maybe different for each sheet.
Thanks
Lee

Hi Lee,

Sorry, but eh?

Specifically, when you say, "that the cells that are to be coloured, maybe different for each sheet", do you mean that you want different cells (EX: Sheet 2, cells B1 and H2, Sheet 3, cells F3 and J12) colored the same as the cell on Sheet 1, or, do you mean that you want the cells on the different sheets to be a different color? I think you mean different ranges (ie- cells or cell addresses).

Happy to help (you made good efforts by the way), and sorry for asking for specificity, but wanted to make sure my (or anyone else's) :friends: answer is truly helpful,

Mark

[GTO]

Addendum:

@Bob:
Please disregard my question at #7. I can certainly make a better effort before asking for an explanation. :think: Curiousity overtook...

Mark

[nbqleebarnes]

Hi Mark,
Your first statement is correct, I would like to have different cells on various sheets the same colour as the cell in sheet 1.
Thanks
Lee

[GTO]

Hello Lee,

Thank you for the clarification. In this case, while using IF statements, your code should have looked more akin to:


Private Sub Workbook_SheetActivate(ByVal Sh As Object)
If Sh.Name = "2" Then
'// If A1 on Sheet1 is NOT empty of color...//
If Not Sheet1.Cells(1, 1).Interior.ColorIndex = xlNone Then
'// ...then we'll make it the same color as A1.//
Sheet2.Range("A1:M1, A2:A30, B30:M30, M2:M29").Interior.ColorIndex = _
Sheet1.Cells(1, 1).Interior.ColorIndex
Else
'// Else, if A1 on Sheet1 is colorless, we'll empty the//
'// other cells of color too :-) //
Sheet2.Range("A1:M1, A2:A30, B30:M30, M2:M29").Interior.ColorIndex = xlNone
End If
ElseIf Sh.Name = "3" Then
'// If A1 on Sheet1 is NOT empty of color...//
If Not Sheet1.Cells(1, 1).Interior.ColorIndex = xlNone Then
'// ...then we'll make it the same color as A1.//
Sheet3.Range("A1:M1, A2:A30, B30:M30, M2:m29").Interior.ColorIndex = _
Sheet1.Cells(1, 1).Interior.ColorIndex
Else
'// Else, if A1 on Sheet1 is colorless, we'll empty the//
'// other cells of color too :-) //
Sheet3.Range("A1:M1, A2:A30, B30:M30, M2:M29").Interior.ColorIndex = xlNone
End If
'ElseIf etc, etc
End If
End Sub

As you are obviously making not only efforts in the imediate solution, but in learning as well, please try this code first, and read through it to see what it is doing.

I would note that one of the easier (or clearer) ways of doing this is to reduce the size of the VBE window, so you can see most of the worksheet, and then place a BreakPoint at the sub's name. Then just step-thru (F8 repeatedly) the code, and you can watch it happen :) ).

Anyways, when looking at the IF's, the problem was that the initial test only succeeded IF we just went to Sheet 2. The rest of the tests were nested, thus, never reached, if we were not on Sheet 2. Does that make sense?

Now technically, we could write it like:


If Sh.Name = "1" Then
'Do whatever
End If
If Sh.Name = "2" Then
'Do whatever
End If
'etc

But using the ElseIf is just shorter, while providing an equivelant test.
Ready for the next step? After testing the above, let's try a Select Case statement. These are handy when there are multiple possible conditions.


Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Select Case Sh.Name
'// NOTE!: I used JUST the numeric value, as there was not alpha character. //
'// Normally (let's say the sheet name was "MySheet", there would be quote //
'// marks req'd. //
Case 2
'// If A1 on Sheet1 is NOT empty of color...//
If Not Sheet1.Cells(1, 1).Interior.ColorIndex = xlNone Then
'// ...then we'll make it the same color as A1.//
Sheet2.Range("A1:M1, A2:A30, B30:M30, M2:M29").Interior.ColorIndex = _
Sheet1.Cells(1, 1).Interior.ColorIndex
Else
'// Else, if A1 on Sheet1 is colorless, we'll empty the//
'// other cells of color too :-) //
Sheet2.Range("A1:M1, A2:A30, B30:M30, M2:M29").Interior.ColorIndex = xlNone
End If
Case 3
'// If A1 on Sheet1 is NOT empty of color...//
If Not Sheet1.Cells(1, 1).Interior.ColorIndex = xlNone Then
'// ...then we'll make it the same color as A1.//
Sheet3.Range("A1:M1, A2:A30, B30:M30, M2:m29").Interior.ColorIndex = _
Sheet1.Cells(1, 1).Interior.ColorIndex
Else
'// Else, if A1 on Sheet1 is colorless, we'll empty the//
'// other cells of color too :-) //
Sheet3.Range("A1:M1, A2:A30, B30:M30, M2:M29").Interior.ColorIndex = xlNone
End If
'Case (etc)
End Select
End Sub

Well... hopefully that was of help.

Have a terrific day,

Mark

[nbqleebarnes]

Hi Mark,
I stepped through the code you said and it makes perfect sense, I see that I didn't have the End IF statement in the correct place.
I thought about using the "Case" command, as I read about it from another thread, I think it was one from XLD.
But thanks a great deal, I can now finish off my WB.
By the way the WB is to make it easier for me to change the colour on multiple sheets for a border, where I have to use various colours for different applications.
Thanks Again.
Cheers
Lee

[GTO]

Very happy to help and cheers to you too :-)

Mark