Results 1 to 5 of 5

Macro to Fill Specific Rows and Columns with Color Based on Cell Value

This is a discussion on Macro to Fill Specific Rows and Columns with Color Based on Cell Value within the Excel Questions forums, part of the Question Forums category; Normal 0 MicrosoftInternetExplorer4 /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin:0in; mso-para-margin-bottom:.0001pt; ...

  1. #1
    Board Regular
    Join Date
    Sep 2010
    Posts
    242

    Default Macro to Fill Specific Rows and Columns with Color Based on Cell Value

    Hello all,

    Just need a little help writing a macro that will fill certain cells with a color across several columns based on a cell value in one column.

    If a cell within the column marked “SODS” (identified by the row 1 header), contains the word “OPEN”, then the row that contains the “OPEN” will be filled with a grey color (color index 15) in the following columns: “Excl”, “Notes”, and “Site” (all noted in row 1 header).

    Help is most appreciated – thank you!

  2. #2
    Board Regular
    Join Date
    Sep 2010
    Posts
    242

    Default Re: Macro to Fill Specific Rows and Columns with Color Based on Cell Value

    It would be something like:

    If column marked "SODS" contains "OPEN" THEN

    Fill row containing "OPEN" with color index 15 in columns marked “Excl”, “Notes”, and “Site”

  3. #3
    Board Regular
    Join Date
    Sep 2010
    Posts
    242

    Default Re: Macro to Fill Specific Rows and Columns with Color Based on Cell Value

    SODS can be in any column, which is why I'd like to use the header in row 1 to identify (and why I'd like to do this as a macro and not use conditional formatting).

    Like this (grey represents grey fill for the cell):

    Before:

    SODS Excl Notes Site




    OPEN






    OPEN






    OPEN




    After:

    SODS Excl Notes Site




    OPEN grey
    grey
    grey




    OPEN grey
    grey
    grey




    OPEN grey
    grey
    grey

  4. #4
    Banned user
    Join Date
    Nov 2010
    Posts
    2,075

    Default Re: Macro to Fill Specific Rows and Columns with Color Based on Cell Value

    Code:
    Sub colorsetc()
    Dim csods As Long, lrsods As Long
    Dim e As Variant, f As Variant
    csods = Application.Match("SODS", Cells.Resize(1), 0)
    If IsError(csods) Then Exit Sub
    lrsods = Cells(Rows.Count, csods).End(3).Row
    For Each f In Array("Excl", "Notes", "Site")
        xx = Application.Match(f, Cells.Resize(1), 0)
        If Not IsError(xx) Then
        For Each e In Cells(1, csods).Resize(lrsods)
            If e.Value = "OPEN" Then Cells(e.Row, xx).Interior.ColorIndex = 15
        Next e
        End If
    Next f
    End Sub

  5. #5
    Board Regular
    Join Date
    Sep 2010
    Posts
    242

    Default Re: Macro to Fill Specific Rows and Columns with Color Based on Cell Value

    Thanks Mirabeau - that worked!

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