Conditional Fomatting 3 in code
 

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!


--
Nuttychick
------------------------------------------------------------------------
Nuttychick's Profile: http://www.excelforum.com/member.php...o&userid=23017
View this thread: http://www.excelforum.com/showthread...hreadid=539786

Conditional Fomatting 3 in code
 

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


--
mudraker
------------------------------------------------------------------------
mudraker's Profile: http://www.excelforum.com/member.php...fo&userid=2473
View this thread: http://www.excelforum.com/showthread...hreadid=539786

Conditional Fomatting 3 in code
 

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

Code:
--------------------
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim WatchRange As Range
Dim CellVal As String
'If Target.Cells.Count 1 Then Exit Sub
'If Target = "" Or Not IsNumeric(Target) Then Exit Sub
CellVal = Target
Set WatchRange = Range("G1:DX42")

If Not Intersect(Target, WatchRange) Is Nothing Then
Select Case CellVal
Case "VRF"
Target.Interior.ColorIndex = 5
Case "Alignment Review"
Target.Interior.ColorIndex = 10
Case "BSD Big Picture Event"
Target.Interior.ColorIndex = 6
Case "Cost Benefit Workshop"
Target.Interior.ColorIndex = 46
Case "DSB Detailed Event"
Target.Interior.ColorIndex = 45
Case "IT Executive Review"
Target.Interior.ColorIndex = 45
Case "IT Supplier Proposal Issued"
Target.Interior.ColorIndex = 45
Case "Plan Complete"
Target.Interior.ColorIndex = 45
Case "Requirements Solution Workshop"
Target.Interior.ColorIndex = 45
Case "Viability Report"
Target.Interior.ColorIndex = 45
Case "Landing Slot"
Target.Interior.ColorIndex = 45
End Select
End If


End Sub
--------------------

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???


--
Nuttychick
------------------------------------------------------------------------
Nuttychick's Profile: http://www.excelforum.com/member.php...o&userid=23017
View this thread: http://www.excelforum.com/showthread...hreadid=539786

Conditional Fomatting 3 in code
 

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!