PDA

View Full Version : Formatting



mahadeshwar
05-11-2006, 04:26 AM
Hi please can you help

i have got some brilliant code from one of your posts it is for conditonal formatting

But it formats all the cells in a spreadsheet. How do i specify it to only format cells in the F coloun of the excel spreadsheet

see below

Many Thanks

Jai


Private Sub Worksheet_Change(ByVal Target As Range)

Dim Cell As Range
Dim Rng1 As Range


On Error Resume Next
Set Rng1 = ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas, 1)
On Error GoTo 0
If Rng1 Is Nothing Then
Set Rng1 = Range(Target.Address)
Else
Set Rng1 = Union(Range(Target.Address), Rng1)
End If
For Each Cell In Rng1
Select Case Cell.Value
Case vbNullString
Cell.Interior.ColorIndex = xlNone
Cell.Font.Bold = False
Case 1 To 5
Cell.Interior.ColorIndex = 5
Cell.Font.Bold = True
Case 16 To 25
Cell.Interior.ColorIndex = 3
Cell.Font.Bold = True
Case 11 To 15
Cell.Interior.ColorIndex = 7
Cell.Font.Bold = True
Case Else
Cell.Interior.ColorIndex = xlNone
Cell.Font.Bold = False
End Select
Next

End Sub

Bob Phillips
05-11-2006, 06:01 AM
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cell As Range
Dim Rng1 As Range

If Not Intersect(Target, Columns(6)) Is Nothing Then
On Error Resume Next
Set Rng1 = ActiveSheet.Columns(6).SpecialCells(xlCellTypeFormulas, 1)
On Error GoTo 0
If Rng1 Is Nothing Then
Set Rng1 = Range(Target.Address)
Else
Set Rng1 = Union(Range(Target.Address), Rng1)
End If
For Each Cell In Rng1
Select Case Cell.Value
Case vbNullString
Cell.Interior.ColorIndex = xlNone
Cell.Font.Bold = False
Case 1 To 5
Cell.Interior.ColorIndex = 5
Cell.Font.Bold = True
Case 16 To 25
Cell.Interior.ColorIndex = 3
Cell.Font.Bold = True
Case 11 To 15
Cell.Interior.ColorIndex = 7
Cell.Font.Bold = True
Case Else
Cell.Interior.ColorIndex = xlNone
Cell.Font.Bold = False
End Select
Next
End If

End Sub

mahadeshwar
05-11-2006, 06:07 AM
THANK YOU SO MUCH YOUR HELP YOU ARE THE KING!!!!

mahadeshwar
05-11-2006, 07:19 AM
In my spread sheet the value of column F is Column D*Column E so when the value is worked out it does not change the colour of the cell as programmed in the script. I have to manually type the value into the cell for the condtional format to work why is that

Do i need something in the code???

See below


Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cell As Range
Dim Rng1 As Range

If Not Intersect(Target, Columns(6)) Is Nothing Then
On Error Resume Next
Set Rng1 = ActiveSheet.Columns(6).SpecialCells(xlCellTypeFormulas, 1)
On Error GoTo 0
If Rng1 Is Nothing Then
Set Rng1 = Range(Target.Address)
Else
Set Rng1 = Union(Range(Target.Address), Rng1)
End If
For Each Cell In Rng1
Select Case Cell.Value
Case vbNullString
Cell.Interior.ColorIndex = xlNone
Cell.Font.Bold = False
Case 1 To 5
Cell.Interior.ColorIndex = 5
Cell.Font.Bold = True
Case 16 To 25
Cell.Interior.ColorIndex = 3
Cell.Font.Bold = True
Case 11 To 15
Cell.Interior.ColorIndex = 7
Cell.Font.Bold = True
Case Else
Cell.Interior.ColorIndex = xlNone
Cell.Font.Bold = False
End Select
Next
End If

End Sub
***************

lucas
05-11-2006, 08:53 AM
Try this, an old one from Jake. Works with formula's

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim cel As Range
For Each cel In Range("F1:F59").Cells
If IsNumeric(cel.Value) And cel.Value <> "" Then
If cel.Value >= 0 And cel.Value < 6 Then
cel.Interior.ColorIndex = 5
cel.Font.ColorIndex = 2
cel.Font.Bold = True

ElseIf cel.Value >= 6 And cel.Value < 15 Then
cel.Interior.ColorIndex = 3
cel.Font.ColorIndex = 2
cel.Font.Bold = True

ElseIf cel.Value >= 15 And cel.Value < 20 Then
cel.Interior.ColorIndex = 46
cel.Font.ColorIndex = 2
cel.Font.Bold = True

Else 'default conditions
cel.Interior.ColorIndex = 0
cel.Font.ColorIndex = 1
cel.Font.Bold = False
End If
End If
Next
End Sub