VBA Conditional Formatting

[dgt]

Hi all

I need to add a fourth condition to the existing three conditions in column J of a particular worksheet. All current 3 conditions work perfectly but the fourth condition needs to change the font colour to "bold & red" when a value greater than zero is entered. The background colour remains white.

I have attached a sample workbook showing the current 3 conditions.

I came across some VBA examples of Conditional Formatting but could not get them to work with my requirements.

I would be greatful for any help in resolving this ...dgt

[Bob Phillips]

You need event code, like so (I leave you to add the other formats



Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Me.Columns("J")) Is Nothing Then

With Target

Select Case True

Case .Value2 = "" And Me.Cells(.Row, "A").Value2 <> ""

.Interior.ColorIndex = 15

Case Date > Me.Range("I3").Value - 7 And Date <= Me.Range("I3").Value

Case .Value2 = "D/O"

Case .Value2 > 0

End Select
End With
End If
End Sub

[dgt]

Hi xld

Thanks for the quick reply. I have amended the code as best that I can but it is not quite functioning as it should.

The new 4th condition was only meant to apply to the cell when a numeric value is greater than £0.00 and not when the cell simply contains anything > 0.

The other problem is that when the status of a cell changes to a different entry, the previous font colour and background are retained even though the new condition is different.

Not sure if I have explained this properly but hopefully the attached workbook (Test worksheet) will make things clearer.

dgt

[Bob Phillips]

The new 4th condition was only meant to apply to the cell when a numeric value is greater than £0.00 and not when the cell simply contains anything > 0.

The subtlety of that difference eludes me, you will have to explain.


The other problem is that when the status of a cell changes to a different entry, the previous font colour and background are retained even though the new condition is different.

Then you have to change/reset those attributes as well.

[dgt]

Using the current code on column 'J' in the test worksheet; when I type the character 'X' in J6 or 'P' in J7 the font colour goes red but I only want this change to apply when a value greater than £0.00 is entered in the cell e.g. £3.75.

Hope this helps to clarify the situation ...dgt

[Bob Phillips]

Change the last condition to



Case IsNumeric(.Value2) And .Value2 > 0

[dgt]

Change the last condition to



Case IsNumeric(.Value2) And .Value2 > 0



Hi xld

Thanks for your efforts, I did'nt manage to get this working in VBA but it may be that I need to spend more time on it. I wondered if it might work better using some form of ELSE IF statements.

Whilst trying this I realised that I could make it work with 3 CF conditions by simply changing the default font colour of that particular column. So its solved temporarily but I will still try to work on a VBA alternative.

Not sure if I should ask this in this thread but do you know if its possible to create a centre tab with VBA so numeric values are centred on the 'point' and text is just centred as normal. MS Word has had this feature for years but I don't know of any way of doing this in Excel.

Regards ...dgt