highlighting range relative to loop

[phibes6]

hey everyone,

im trying to run a macro that fills a range of cells relative to a cell based on the value.

essentially looped down the rows i want to:
if h2 = value
then select a22 and fill and outline

heres what ive got so far, i just cant get the relative selection figured out

Sub MyMacro()
For Each c In Range("H2:H250")
If c = "value" Then
Range("A2:P2").Select
'what do i put here?
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 10040319
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
Else
End If
Next
End Sub

Thanks everyone!

 

[stnkynts]

Try:

Option Explicit
Sub ColorCells()
Dim lastrow As Long, icell As Long
 
'if you dont want to specify a range you could use this
lastrow = Range("H" & Rows.Count).End(xlUp).Row
 
'could also be For icell = 2 to lastrow
For icell = 2 To 250
    If Range("H" & icell).Value = "insert value here" Then
        Range("A" & icell, "P" & icell).Interior.ColorIndex = 3
        'insert rest of formatting code here
    End If
Next icell
 
End Sub

Note the areas in the code where you can put the rest of your formatting code. like for example the border type that you want.

 

[phibes6]

works perfectly, thanks!