Conditional Fomatting >3 in code

[Nuttychick]

Hi need a bit of help coding the equivalent to conditional formatting, need to check a block of cells for certain values and format the colour if the condition is true – think it would be ok for the range to be by columns - the rows are variable but I can make the columns static.
Simple conditions all based on the text in the cell. i.e. =”VRF” then colour yellow. etc, problem is I need to specify this for about 12 different instances.
Have tried some of the code in the forums, but having problems with it running.
Tried using this code from one of the threads but being a bit thick on actually putting the code in the right place, this is all the macro needs to do.
I created a new macro and ended up with the below, but not sure what to do with the top few lines to set the macro up correctly.

Please Login or Register  to view this content.

Tried a few variations, i.e.

Please Login or Register  to view this content.

Please can anyone advise where I am going wrong and perhaps explain a resolution to me!

[mudraker]

Nuttychick

You was so close

This needs to go in the worksheet Module
Right Click on thew Sheet name tab
Select View Code
Paste the following into the worksheet module

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
'turn event trigger off whilst macro running
Application.EnableEvents = False
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
'turn event trigger back on
Application.EnableEvents = True
End With
End Sub

[Nuttychick]

ok - so have played around a bit and now have the following - which works when you type new values into the sheet.

Please Login or Register  to view this content.

However I would like to be able to paste in updated data and for the sheet to automatically format.

I can do this with individual cells, but if I try to copy and paste more than one cell I get Datatype mismatch.

Anyone know what I need to do to allow me to copy and paste a whole spreadsheet of new information in, and for the sheet to accept it and automatically update with formatting???

[Paul Lautman]

Sounds like you need setstyle by Lee Mosqueda
(http://www.geocities.com/lee_m2/addins.html). He hasn't published the Excel
version of it yet. I'll try to contact him and see if he's OK with it being
released.

Nuttychick wrote:
> Hi need a bit of help coding the equivalent to conditional formatting,
> need to check a block of cells for certain values and format the
> colour if the condition is true - think it would be ok for the range
> to be by columns - the rows are variable but I can make the columns
> static. Simple conditions all based on the text in the cell. i.e.
> ="VRF" then colour yellow. etc, problem is I need to specify this
> for about 12 different instances.
> Have tried some of the code in the forums, but having problems with it
> running.
> Tried using this code from one of the threads but being a bit thick on
> actually putting the code in the right place, this is all the macro
> needs to do.
> I created a new macro and ended up with the below, but not sure what
> to do with the top few lines to set the macro up correctly.
>
> Code:
> --------------------
> Sub Macro6()
> '
> ' Macro6 Macro
> ' Macro recorded 08/05/06 by BZRMC3
> '
>
> '
> 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
> --------------------
>
>
> Tried a few variations, i.e.
>
>
> Code:
> --------------------
> Private Sub Macro6 (ByVal Target As Range)
> '
> ' Macro6 Macro
> ' Macro recorded 08/05/06 by BZRMC3
> 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
> --------------------
>
>
>
> Please can anyone advise where I am going wrong and perhaps explain a
> resolution to me!