Results 1 to 3 of 3

Change cell color according to other cells value/position

This is a discussion on Change cell color according to other cells value/position within the Excel Questions forums, part of the Question Forums category; I have solved the problem, for the post: get colour from one cell and change another cell to that colour ...

  1. #1
    Board Regular
    Join Date
    Jun 2002
    Location
    UK / Somerset
    Posts
    62

    Default

    I have solved the problem, for the post:

    get colour from one cell and change another cell to that colour

    http://www.mrexcel.com/board/viewtop...1410&forum=2&9

    These VBA scripts may help others who struggle to succeed

    Thanks to: technix, zzydhf, Nimrod

    ..::Tom Powley::..




    Purpose:
    Colour the active cell with the colour of another cell, which is 21 down, and 7 across.

    Code:


    Sub CellColourChange()
    '
    ' CellColourChange Macro
    ' Macro recorded 14/06/2002 by Research Machines plc
    '
    ActiveCell.Interior.ColorIndex = ActiveCell.Offset(21, 7).Range("A1").Interior.ColorIndex
    End Sub






    Purpose:
    This procedure will fire every time a value in Column E is changed. When activated
    this procedure will change to corresponding cell in Column A's intertior.color
    to match the color in E.

    -select the sheet you want the procedure to work in
    -right click on the tab of the sheet you want the procedure to work in
    -left click on the "viewcode" that is displayed in drop down menu..
    VBE will open
    -paste procedure in the large right window of the VBE

    Code:


    Private Sub Worksheet_Change(ByVal Target As Range)
    With Target
    If .Column = 5 Then Cells(.Row, 1).Interior.ColorIndex = .Interior.ColorIndex
    End With
    End Sub


    ..::Nimrod::..






    I worked this one out for myself - my very first bit of VBA scripting!!

    Purpose:
    This macro will search through cells A1 to A20 and any cells that contain the same value as the active cell will be then coloured the same as the active cell.

    Code:


    Sub FindCellAndUseCol()
    '
    ' FindCellAndUseCol Macro
    ' Macro recorded 15/06/2002 by -
    '
    With Range("a1:a20")
    Set c = .Find(ActiveCell, LookIn:=xlValues)
    If Not c Is Nothing Then
    firstAddress = c.Address
    Do
    c.Interior.ColorIndex = ActiveCell.Interior.ColorIndex
    Set c = .FindNext(c)
    Loop While Not c Is Nothing And c.Address <> firstAddress
    End If
    End With
    End Sub






  2. #2
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,702

    Default

    Nice T!

    Should we keep this post with the original thread?

    If you respond to the original thread, it'll be bumped up for us to see and we can see the entire solution coming together.

    Food for thought, makes it easier for people (like me) to follow...

    Glad to hear all is well. Have a nice weekend.

  3. #3
    Board Regular
    Join Date
    Jun 2002
    Location
    UK / Somerset
    Posts
    62

    Default

    You are right,

    I'll do it now

    Hope this helps others and once again thank you everyone!!

    ..::Tom::..

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com