Need help for macro to conditionally format a selected range

**smusyaju** · 10-30-2014, 01:19 PM

Hi All,

I have data as below:
COL1 COL2 COL3 COL4 COL5 COL6 COL7 COL8
A A A A A A A 1
A A A A A A A 1
A A A A A A A 1
A A A A A A A 1
A A A A A A A 2
A A A A A A A 2

what I need to do is if col8 = 1, then format the range col3 through col7 (lets say make font.name = "Arial") and if col8 = 2, then format col1 through col2. Can somebody please help me create a Macro for this requirement.

Excel 2007

Thanks

**Richard Buttrey** · 10-30-2014, 01:41 PM

Hi,

You don't of course NEED a macro for this. A simple conditional format will do.

In G2 create two new rules by entering the following conditional formats using the 'Use a formula...option

Formula:

Please Login or Register  to view this content.

set the appropriate formats, font,colour etc.
then in the CF Wizard set the 'Applies to' range to C2:C100

add a second rule

Formula:

Please Login or Register  to view this content.

set the format for this and apply it to A1:B100

**smusyaju** · 10-30-2014, 02:07 PM

Thanks Richard, But with conditional formatting, I cannot change the font, all I can change is font style, color and underline stuffs only.

**smusyaju** · 10-30-2014, 04:44 PM

Thanks Richard. I did it anyway with a Macro.

Sub format_it()
Dim rownm As Integer, colmn As Integer, i As Integer, j As Integer
rownm = 100
colnm = 7
For i = 2 To rownm
If Cells(i, 8).Value = 1 Then
For j = 3 To colnm
Cells(i, j).Interior.Color = vbYellow
Cells(i, j).Font.Name = "Arial"
Cells(i, j).Font.Size = "8"
Cells(i, j).Borders(xlEdgeBottom).LineStyle = xlContinuous
Cells(i, j).Borders(xlEdgeBottom).Weight = xlMedium
Next j
End If

If Cells(i, 8).Value = 2 Then
For j = 1 To 2
Cells(i, j).Interior.Color = vbYellow
Cells(i, j).Font.Name = "Arial"
Cells(i, j).Font.Size = "8"
Cells(i, j).Borders(xlEdgeBottom).LineStyle = xlContinuous
Cells(i, j).Borders(xlEdgeBottom).Weight = xlMedium
Next j
End If

Next i

End Sub