How do I autofill cell color based on other cells' color in Excel?

K

Kchatzi

Hello to everyone! :bigwave:

This is what i want to do.. Let's say there is table with columns A and B.

When i fill cell A2 with green color, i want cell B2 to auto fill with green color as well.. Is that possible? I think it requires VBA but i'm not a programmer.. :nope:

So if you please help me, it's work related and it would help me a lot!!

Thank you very much! :)
 

Celtic Warrior

Involved In Discussions
Re: How do i auto fill cell color based on other cells' color?

Hi Kchatzi
Welcome to the cove.
Here is some VBA code that could do this for you.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Range("a2").Interior.ColorIndex = 15 Then
Range("b2").Interior.ColorIndex = 15
Else: Range("A1").Value = ""
End If
End Sub

Hope it helps

CW
 
K

Kchatzi

Re: How do i auto fill cell color based on other cells' color?

Thank you for your reply!

So i create a module and copy-paste this code.. Then what?
 
T

tomvehoski

You can use the conditional formatting feature for up to three colors. For example:

Highlight cells A1 and B1
Format - Conditional Formatting
Select "Forumula Is", $A$1 = 1, then click the Format button to change the color, font style, etc.
Click Add to put in two more conditions.

When done, A1 and B1 will be colored based on the value in A1. You can hide the text if you want by making the font the same color as the background. This is Excel 2003 by the way.
 

Michael_M

Trusted Information Resource
You may also try 'Conditional Formatting' This may do what you want as well without having to run a macro. I use conditional formatting to change the color of a cell if the number is not between the value in two other cells (to show a typo if one is made).
 
D

Darius

Re: How do i auto fill cell color based on other cells' color?

Nope, You don't need to add a module, you just add to the sheet's code page.

Excel Sheets allow the programmers to customize their objects methods

The VBA MACRO uses the Sheet's internal methods for event detection, the event it self is "selection change", this method is triggered each time you change from cell to cell, not the color change but the cell selection change (the actual cell).

If you don't know how to enter VBA editor, if you don't see developer tab, press the Office Button and Excel Options, in Popular, the third row of checkboxes "show developer tab", just check it.
Then in the developer tab, select visual Basic and click on the sheet you want to modify that method, and paste to it. Back on the sheet you will see the effect of this method changed. But beware, the code just change the text to nothing, no the color.

A modiffication to the code that may work out.

Dim OLD_SELECTION As Range, OLD_COLOR As Long

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If OLD_SELECTION Is Nothing Then
Set OLD_SELECTION = Target
OLD_COLOR = OLD_SELECTION.Interior.Color
end if
If Range(OLD_SELECTION.Address).Column = 1 And OLD_COLOR <> OLD_SELECTION.Interior.Color Then
Cells(OLD_SELECTION.Row, 2).Interior.Color = OLD_SELECTION.Interior.Color
End If
Set OLD_SELECTION = Target
OLD_COLOR = OLD_SELECTION.Interior.Color
End Sub
 
Last edited by a moderator:
B

buzzjaw

Re: How do i auto fill cell color based on other cells' color?

You might want to have a look at this code. It goes in the worksheet's code and colours cells based on their value and being in a specific range (MyRange). The Ucase code means that the entries aren't case sensitive, but to be honest I prefer to control the entries so that users can't enter words in any old form. It makes life alot easier when you are trying to carry out computations if you control the entry format. What's really nice about this is that there are fewer limits to the number of colours and cell values that you can apply this to.

Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("MyRange")) Is Nothing Then

Else
Select Case UCase(Target.Value)
Case Is = "CONFORMING"
Target.Interior.ColorIndex = 10
Case Is = "NONCONFORMING"
Target.Interior.ColorIndex = 3
Case Else
Target.Interior.ColorIndex = 0
End Select

End If

End Sub
 
N

naveeddil

Re: How do i auto fill cell color based on other cells' color?

Nope, You don't need to add a module, you just add to the sheet's code page.

Excel Sheets allow the programmers to customize their objects methods

The VBA MACRO uses the Sheet's internal methods for event detection, the event it self is "selection change", this method is triggered each time you change from cell to cell, not the color change but the cell selection change (the actual cell).

If you don't know how to enter VBA editor, if you don't see developer tab, press the Office Button and Excel Options, in Popular, the third row of checkboxes "show developer tab", just check it.
Then in the developer tab, select visual Basic and click on the sheet you want to modify that method, and paste to it. Back on the sheet you will see the effect of this method changed. But beware, the code just change the text to nothing, no the color.

A modiffication to the code that may work out.

Dim OLD_SELECTION As Range, OLD_COLOR As Long

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If OLD_SELECTION Is Nothing Then
Set OLD_SELECTION = Target
OLD_COLOR = OLD_SELECTION.Interior.Color
end if
If Range(OLD_SELECTION.Address).Column = 1 And OLD_COLOR <> OLD_SELECTION.Interior.Color Then
Cells(OLD_SELECTION.Row, 2).Interior.Color = OLD_SELECTION.Interior.Color
End If
Set OLD_SELECTION = Target
OLD_COLOR = OLD_SELECTION.Interior.Color
End Sub


Dear Daurius,

I have tried the code and its working fine but i want a little change that i was to write 1 for RED, 0 for GREEN, and 2 for YELLOW

Can you please guide.
 
D

Darius

This code works as the other, but according to the cell color (if you move throught the cell) add a 0,1,2 according to the actual cell color (I used the 16 color, colors => vbgreen, vbyellow, vbred).

++++++++++++++++++++++
If you want other colors, the color can be obtained with the code:

Sub Cell_Color()
msgbox ActiveCell.Interior.Color
End Function

Just select the cell with the color and execute the macro with macro execution in excel, but you will need to put this code in a module.
+++++++++++++++++++++++

Code:
Dim OLD_SELECTION As Range, OLD_COLOR As Long
Const GREEN = 65280
Const RED = 255
Const YELLOW = 65535

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If OLD_SELECTION Is Nothing Then
  Set OLD_SELECTION = Target
  OLD_COLOR = OLD_SELECTION.Interior.Color
End If
If Range(OLD_SELECTION.Address).Column = 1 Then
  Select Case (OLD_SELECTION.Interior.Color)
    Case (RED): Cells(OLD_SELECTION.Row, 2).Value = 2
    Case (YELLOW): Cells(OLD_SELECTION.Row, 2).Value = 1
    Case (GREEN): Cells(OLD_SELECTION.Row, 2).Value = 0
  End Select
End If
Set OLD_SELECTION = Target
OLD_COLOR = OLD_SELECTION.Interior.Color
End Sub
 
N

naveeddil

Thank You dear for rescuing me on this forum although you were idle for long from here.

Can you please look into my file where i want the code to be appear right in fornt of the color.


Thank You
 

Attachments

  • Sample Data1.xlsm
    18.9 KB · Views: 427
Top Bottom