[F1fan81]

I am using MS Excel 2003.

I have a formula in cell N11 =ROUNDDOWN(M11/60,0) &
a formula in cell O11 =M11-(N11*60)

I want the text in this cell to change color in cells N11 & O11 depending on the calculation M11-F11.

There are also formulas in Cells F11 & M11. These are:

F11 =SUM(F4:F10)
M11 =SUM(M4:M10)

I would like it to work like this:

If M11 is equal to or greater than F11 then the answer to the formulas in cells N11 & O11 should have the text displayed in black.

If M11 is less than F11 then the answer to the formulas in cells N11 & O11 should have the text displayed in red.

Is this possible?

 

[AlbertMC2]

Hi

There are 2 ways to do this.
1. Use conditional formatting.
Click on N11 then go to Format -> Conditional Formatting -> Change to
"Cell Value Is" "Equal to" "=IF(F11>M11,N11,0)"
Click on Format -> Change Text Colour to Red -> OK -> OK

Click on O11 then go to Format -> Conditional Formatting -> Change to
"Cell Value Is" "Equal to" "=IF(F11>M11,O11,0)"
Click on Format -> Change Text Colour to Red -> OK -> OK

(Note there may be easier ways to do this with conditional formatting)

2. Use VBA
You can create a procedure linked to the on SheetChange event.
Press Ctrl + F11 to open the VB Editor
On the left side double click on "This Workbook"
Make the object = "Workbook" and the procedure = "SheetChange"
(1st and 2nd comboboxes at the top, right window)
You should then get a :
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

Enter (or copy and paste)

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
   With Worksheets("Sheet1")
        If .Range("F11") > .Range("M11") Then 'set to red
            .Range("N11:O11").Font.ColorIndex = 3
        Else 'set to black
            .Range("N11:O11").Font.ColorIndex = 1
        End If
    End With
End Sub

Save everything, Exit the VBA editor. And hopefully that works. Everytime that your workbook changes this code will run comparing Cells F11 and M11 and updating the Font colour on Cells N11 and O11.