If Statements Question

[Guest]

I am working on some macros to automate some organization of data for parts
inventory management purposes, and reporting. I wanted to have certain rows
get highlighted automatically when the data is all imported and it meets
certain criteria. FOR INSTANCE:

If COLUMN D is greater than 3, bold the entire ROW with X coloring. What is
the most efficient method for this? Perhaps even step it out further and put
in ranges to bold different collors instead of just a blanket greater than
(ie: between 3 and 6 bold X color, between 7 and 10 bold X color, etc. )

 

[macropod]

Hi,

If you only need four colours, including the default one, conditional
formatting might fulfil your needs, without resorting to vba.

For more than four colours, a vba solution is fairly straightforward. For
example, putting the following code in the Worksheet will automatically
change the colours as the values in Column D change:

Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If .Column = 4 Then
Select Case .Value
Case 0 To 2.99
.EntireRow.Interior.ColorIndex = 4
Case 3 To 5.99
.EntireRow.Interior.ColorIndex = 6
Case 6 To 9.99
.EntireRow.Interior.ColorIndex = 39
Case 10 To 14.99
.EntireRow.Interior.ColorIndex = 41
Case Is >= 15
.EntireRow.Interior.ColorIndex = 3
Case Else
.EntireRow.Interior.ColorIndex = 0
End Select
End If
End With
End Sub

Six csaes are catered for; add or delete as needed.

Cheers

 

[Guest]

Thanks for that

Hi,

If you only need four colours, including the default one, conditional
formatting might fulfil your needs, without resorting to vba.

For more than four colours, a vba solution is fairly straightforward. For
example, putting the following code in the Worksheet will automatically
change the colours as the values in Column D change:

Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If .Column = 4 Then
Select Case .Value
Case 0 To 2.99
.EntireRow.Interior.ColorIndex = 4
Case 3 To 5.99
.EntireRow.Interior.ColorIndex = 6
Case 6 To 9.99
.EntireRow.Interior.ColorIndex = 39
Case 10 To 14.99
.EntireRow.Interior.ColorIndex = 41
Case Is >= 15
.EntireRow.Interior.ColorIndex = 3
Case Else
.EntireRow.Interior.ColorIndex = 0
End Select
End If
End With
End Sub

Six csaes are catered for; add or delete as needed.

Cheers