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

[Michael151]

<!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:Compatibility> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> </w:Compatibility> <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel> </w:WordDocument> </xml><![endif]--><!--[if gte mso 10]> <style> /* 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; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman";} </style> <![endif]--> 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!

 

[Michael151]

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”

 

[Michael151]

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:

<table border="0" cellpadding="0" cellspacing="0" width="256"><col style="width: 48pt;" width="64" span="4"> <tbody><tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt; width: 48pt;" width="64" height="17">SODS</td> <td style="width: 48pt;" width="64">Excl</td> <td style="width: 48pt;" width="64">Notes</td> <td style="width: 48pt;" width="64">Site</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">OPEN</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">OPEN</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">OPEN</td> <td>
</td> <td>
</td> <td>
</td> </tr> </tbody></table>

After:

<table border="0" cellpadding="0" cellspacing="0" width="256"><col style="width: 48pt;" width="64" span="4"> <tbody><tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt; width: 48pt;" width="64" height="17">SODS</td> <td style="width: 48pt;" width="64">Excl</td> <td style="width: 48pt;" width="64">Notes</td> <td style="width: 48pt;" width="64">Site</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">OPEN</td> <td class="xl24">grey
</td> <td class="xl24">grey
</td> <td class="xl24">grey
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">OPEN</td> <td class="xl24"> grey
</td> <td class="xl24"> grey
</td> <td class="xl24"> grey
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">OPEN</td> <td class="xl24"> grey
</td> <td class="xl24"> grey
</td> <td class="xl24"> grey
</td> </tr> </tbody></table>

 

[mirabeau]

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

 

[Michael151]

Thanks Mirabeau - that worked!