Solved: Change font characteristics depending on value

[Aussiebear]

In the attached workbook, I need to change the font characteristics depending on whether the contact is Future, Active or Closed. I could individually apply conditional formatting on a row by row basis by wish to do so by using Code.

As a Contract is entered on the sheet, the font needs to be light purple (or mauve, I can never tell the difference, and non bold). This type of Contract "Future" has zero tons (Column I) recieved against it.

When a Contract becomes 'Active", the font needs to change for the row to Blue and Bold.

When a Contract becomes 'Closed", the font needs to change for that row to Grey and non bold. This type of Contract becomes closed when the value in Column C becomes "Yes".

I have applied a password "Password" to protect the sheet with only the values in Columns I & K unlocked, as this is how the User will see the sheet.
Ocassionally a person with Admin rights will delete Contracts from the sheet, hence the need for the formatting to be applied by code.

[figment]

looks like you had most the code already written. i moved it to on change rather then select, for that made more sense to me.

[VBA]Private Sub Worksheet_Change(ByVal Target As Range)
Dim TonsCell As Range
Dim MyCell As Range
With Range("B2:L" & Range("L2").End(xlDown).Row)
.Font.Bold = False
.Font.Color = 16737996
End With
For Each TonsCell In Range("I2:I200") 'Don't expect more than 200 rows of data at any one time
If TonsCell.Value > 0 Then
With Range("B" & TonsCell.Row, Cells(TonsCell.Row, Columns.Count).End(xlToLeft)) 'Active Row
.Font.Bold = True
.Font.ColorIndex = 5 'or What ever value Blue is
End With
End If
Next
For Each MyCell In Range("C2:C200") 'Don't expect more than 200 rows of data at any one time
If MyCell.Value = "Yes" Then
With Range("B" & MyCell.Row, Cells(MyCell.Row, Columns.Count).End(xlToLeft))
.Font.Bold = False
.Font.ColorIndex = 10 'or What ever value Grey is
End With
End If
Next
End Sub
[/VBA]

[Aussiebear]

Many thanks Figment. I had a go but wasn't sure about a couple of things