To give back ground colour for entire row on certain condition

[pol]

Please let me help to give a colour for the entire row by certain condition
as follows

I have an excel sheet with the following column

A B
Code Qty

001 20
002 30
003 10
004 20
005 15
006 05

if in any of the cell in B is less than that of 10 , the back ground colour
of the entrire row of that cell having that condition = true should be in Red.

Please let me know

With thanks and Regards

Pol

 

[JBeaucaire]

That's conditional formatting, and I suggest you NOT apply conditional
formatting to ENTIRE rows or the entire workbook. Just apply this answer far
enough out to the right to be practical...as far as your data goes and
perhaps a column or two further.

Anyway, highlight all the cells from A2 to whatever your last cell choice,
them click on FORMAT > CONDITIONAL FORMATTING. Use this formula:

Condition1: Formula Is:
=AND(ISNUMBER($B2),$B2<10)
Format...Pattern...select a red color

Now any rows with a value in column B less than 10 will light up.

 

[pol]

Please let me know anybody , how I can write a macro for the mentioned
purpose. I need a sample macros.

Please with thanks

Pol

 

[Jacob Skaria]

If you are really looking for a macro.... try below. Set the Security level
to low/medium in (Tools|Macro|Security). From workbook launch VBE using
short-key Alt+F11. From menu 'Insert' a module and paste the below code.
Save. Get back to Workbook. Run macro from Tools|Macro|Run <selected macro()>

Sub Macro()
Dim lngRow As Long
Dim lngLastRow As Long
lngLastRow = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row
For lngRow = 2 To lngLastRow
If IsNumeric(Range("B" & lngRow)) Then
'If greater than 0 and less than 10
'If Range("B" & lngRow) < 10 Then
If Range("B" & lngRow) > 0 And Range("B" & lngRow) < 10 Then
Rows(lngRow).Interior.ColorIndex = 3
End If
End If
Next
End Sub

 

[pol]

This macro will work but the problem is it will take long time to work this
macros for large number of rows. Is it possible to give the rule of
conditional formating in a macros instead of using the loop.

Please let me know

 

[Jacob Skaria]

Sub Macro()
Dim lngLastRow As Long
Application.ScreenUpdating = False
lngLastRow = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row
Rows("2:" & lngLastRow).Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=AND(ISNUMBER($B2),$B2<10)"
Selection.FormatConditions(1).Interior.ColorIndex = 3
Application.ScreenUpdating = True
End Sub

 

[pol]

Thanks a lot . It is working fine

Sub Macro()
Dim lngLastRow As Long
Application.ScreenUpdating = False
lngLastRow = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row
Rows("2:" & lngLastRow).Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=AND(ISNUMBER($B2),$B2<10)"
Selection.FormatConditions(1).Interior.ColorIndex = 3
Application.ScreenUpdating = True
End Sub