Conditional Formating with Opposite Colours

Hi

 

I have a column in which cells are filled in with a different colours let’s say blue and red.

Is there any way that I could fill another cell based on a character in a third cell?

 

For example:

 

All the cells in column A have to be filled in manually.

Is there any way that if I put character S ( same ) into cell B1 then cell C1 will be filled in with the same colour as A1 and if put O ( opposite ) into cell B2 then cell C2 will be filled in with an opposite colour than A2 for example blue?

Its excel 2010 and windows 7

 

 

A

B

C

1

 

S

 

2

 

O

 

3

 

S

 

4

 

 

 

 

 

 

 

 

Thanks for any help

By: Marcisz

7 thoughts on “Conditional Formating with Opposite Colours

  1. andrew walker Post author

    Hi there

    This would be difficult to impossible to complete using conditional formatting. But, it would work with some VBA…
    The code below should help… 🙂

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim KeyCells As Range
    Dim Red, Blue As Long

    Red = 3 'ColorIndex color
    Blue = 33 'ColorIndex color

    'Set range of cells to watch for changes - this is where you would enter O or S
    Set KeyCells = Range("B1:B1500")

    If Not Application.Intersect(KeyCells, Range(Target.Address)) Is Nothing Then

    If UCase(Cells(Target.Row, 2)) = "S" Then Cells(Target.Row, 3).Interior.ColorIndex = Cells(Target.Row, 1).Interior.ColorIndex
    If UCase(Cells(Target.Row, 2)) = "O" Then
    If Cells(Target.Row, 1).Interior.ColorIndex = Red Then Cells(Target.Row, 3).Interior.ColorIndex = Blue
    If Cells(Target.Row, 1).Interior.ColorIndex = Blue Then Cells(Target.Row, 3).Interior.ColorIndex = Red
    End If

    End If
    End Sub

  2. Marcisz

    Dzięki wielkie za pomoc
    ale czy mógłbyś jeszcze napisać jak mniej więcej zastosować ten kod?

  3. andrew walker Post author

    Hi Marcisz

    Of course. You will need to enable the Developer tab within the Customise Ribbon options. Once you have done that you should then see the Developer tab at the top of the screen in the Ribbon.

    Click on Developer. Then click on Visual Basic. This will open a new window. On the left hand side you should see a list of all the worksheets contained in your excel file. Double click the name of the worksheet that you want the colour formatting to work on. Doing this will open a new window on the right hand side.

    Now, copy the code above and paste it into the window on the right.

    If it helps, I can upload a link here to working excel file that you could download…?

  4. Marcisz

    Hi

    It works great now!
    Just have one final question.

    Would that be a problem to change that code a little bit so that it would use colour from row 1 but one cell above?
    For example when you put S ( same ) into cell B2 it would apply the same colour from cell A1 and if you put O into cell B3 it would apply opposite colour form cell A2 and so on?

    Thanks again for any help

    P.S. for some reason I couldn’t paste a proper chart from excel :-/

  5. andrew walker Post author

    Hi

    This should do that for you… 🙂

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim KeyCells As Range
    Dim Red, Blue As Long

    Red = 3 'ColorIndex color
    Blue = 33 'ColorIndex color

    'Set range of cells to watch for changes - this is where you would enter O or S
    Set KeyCells = Range("B1:B1500")

    If Not Application.Intersect(KeyCells, Range(Target.Address)) Is Nothing Then

    If UCase(Cells(Target.Row, 2)) = "S" Then Cells(Target.Row, 3).Interior.ColorIndex = Cells(Target.Row - 1, 1).Interior.ColorIndex
    If UCase(Cells(Target.Row, 2)) = "O" Then
    If Cells(Target.Row - 1, 1).Interior.ColorIndex = Red Then Cells(Target.Row, 3).Interior.ColorIndex = Blue
    If Cells(Target.Row - 1, 1).Interior.ColorIndex = Blue Then Cells(Target.Row, 3).Interior.ColorIndex = Red

    End If

    End If
    End Sub

    The lines in bold are the ones I have changed for you.

Leave a Reply

Your email address will not be published. Required fields are marked *