Formatting

[mahadeshwar]

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

[VBA]
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
[/VBA]

[Bob Phillips]

[vba]

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
[/vba]

[mahadeshwar]

THANK YOU SO MUCH YOUR HELP YOU ARE THE KING!!!!

[mahadeshwar]

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

[VBA]
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
***************
[/VBA]

[lucas]

Try this, an old one from Jake. Works with formula's
[vba]
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
[/vba]