Hello to everyone!
This is what i want to do.. For example there is a table with columns A
and B.
When i manually fill cell A2 with green color, i want that to
automatically fill cell B2 with green color as well..
I think it requires VBA but i'm not a programmer..
So if you please help me, it's work related and it would help me a
lot!!
Thank you very much!
Hi,
I'm not a specialist, possible that there is a better approach, but tried following code.
copy & paste following code in your worksheet module
Option Explicit
'first we define 2 static row pointers
Dim OldRow As Long
Dim NewRow As Long
Private Sub Worksheet_Activate()
'when this worksheet becomes active, save the actual rownumber into OldRow
OldRow = ActiveCell.Row
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'save the actual rownumber into NewRow
NewRow = ActiveCell.Row
'if for example OldRow= 0, then preset it to the first row
If OldRow = 0 Then OldRow = 1
'following code works for the cells A1 to A65535
If Not Intersect(Target, Range("A1:A65535")) Is Nothing Then
If Cells(OldRow, 1).Interior.ThemeColor = 0 Then
'we deal with the standard colors
Cells(OldRow, 2).Interior.ColorIndex = Cells(OldRow, 1).Interior.ColorIndex
'we deal with the new theme colors from Excel 2007 / 2010
Else
With Cells(OldRow, 2).Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = Cells(OldRow, 1).Interior.ThemeColor
.TintAndShade = Cells(OldRow, 1).Interior.TintAndShade
.PatternTintAndShade = 0
End With
End If
OldRow = ActiveCell.Row
End If
End Sub
NOTE:
1) Its not flawless!
2) Even you can use the standard colors, i don't recommend it because the colors aren't the same, even the colorindex is the same - don't know why.
So i recomment to use the theme colors from Excel 2007 / 2010
Success.
Ludo
