Macro to Conditionally format Thick Border

[nuver]

Hello All
I need help wirting a macro to conditionally format a selection based
on the below criteria. The macro will run at the click of a button.

Selection(B2:F18)

If $A2="Y1" then format Border top thick and Interior color light
Yellow

If $A2="Y" then format Border top thin and Interior color light Yellow

If $A2="G1" then format Border top thick and Interior color light
Green

If $A2="G" then format Border top thin and Interior color light Green

Thank you for your help!
Ed

 

[Rowan Drummond]

Maybe like this:

Sub Frmt()
With Range("B2:F18")
Select Case Range("A1").Value
Case "Y1"
.Borders(xlEdgeTop).Weight = xlThick
.Borders(xlInsideHorizontal).Weight = xlThick
.Interior.ColorIndex = 36
Case "Y"
.Borders(xlEdgeTop).Weight = xlThin
.Borders(xlInsideHorizontal).Weight = xlThin
.Interior.ColorIndex = 36
Case "G1"
.Borders(xlEdgeTop).Weight = xlThick
.Borders(xlInsideHorizontal).Weight = xlThick
.Interior.ColorIndex = 35
Case "G"
.Borders(xlEdgeTop).Weight = xlThin
.Borders(xlInsideHorizontal).Weight = xlThin
.Interior.ColorIndex = 35
End Select
End With
End Sub

Hope this helps
Rowan

 

[nuver]

Thanks Rowan
Works exactly as I asked. The problem is I did not ask the questio
correctly. The formatting is what I am looking for but the source an
destination are not exactly what I wanted. I am going to try to explai
myself a little better, sorry I did not get it right the first time
Thanks for the quick response.

If cell A2 meets the criteria I want the formatting to pertain t
B2:F2
If cell A3 meets the criteria I want the formatting to pertain t
B3:F3
If cell A4 meets the criteria I want the formatting to pertain t
B4:F4
If cell A5 meets the criteria I want the formatting to pertain t
B5:F5

and so on....

Thanks again

 

[Rowan Drummond]

I am still not 100% clear on what you require but if you are wanting to
check the value of each cell in the range A2:A18 and set the formating
in columns B:F in each row based on that value then maybe like this:

Sub Frmt()
Dim cell As Range
For Each cell In Range("A2:A18")
With Range(Cells(cell.Row, 2), Cells(cell.Row, 6))
Select Case cell.Value
Case "Y1"
.Borders(xlEdgeTop).Weight = xlThick
.Interior.ColorIndex = 36
Case "Y"
.Borders(xlEdgeTop).Weight = xlThin
.Interior.ColorIndex = 36
Case "G1"
.Borders(xlEdgeTop).Weight = xlThick
.Interior.ColorIndex = 35
Case "G"
.Borders(xlEdgeTop).Weight = xlThin
.Interior.ColorIndex = 35
End Select
End With
Next cell
End Sub

Regards
Rowan

 

[nuver]

Rowan
This is exactly what I was looking for. Thank you for your help.

 

[Rowan Drummond]

You're welcome