need in macro

[zutcom]

I want to change the color of a cell based on the result of it

need a negative to be in different color

like cell h5=345.54 = yellow cell fill but
cell h5 = -1253.50= yellow dark cell fill

can someone help

Z

 

[mumps]

Hi Z. Assuming your values are in column H, try this macro:

Sub test()
    Application.ScreenUpdating = False
    Dim bottomH As Integer
    bottomH = Range("H" & Rows.Count).End(xlUp).Row
    Dim cell As Range
    Dim rng As Range
    Set rng = Range("H2:H" & bottomH)
    For Each cell In rng
        If cell >= 0 Then
            cell.Interior.ColorIndex = 6
        ElseIf cell < 0 Then
            cell.Interior.ColorIndex = 44
        End If
    Next cell
    Application.ScreenUpdating = True
End Sub

 

[zutcom]

Hi Z. Assuming your values are in column H, try this macro:

Sub test()
    Application.ScreenUpdating = False
    Dim bottomH As Integer
    bottomH = Range("H" & Rows.Count).End(xlUp).Row
    Dim cell As Range
    Dim rng As Range
    Set rng = Range("H2:H" & bottomH)
    For Each cell In rng
        If cell >= 0 Then
            cell.Interior.ColorIndex = 6
        ElseIf cell < 0 Then
            cell.Interior.ColorIndex = 44
        End If
    Next cell
    Application.ScreenUpdating = True
End Sub

No it's only the F cell when its negative
not working so far
positive color : R255, G255, B204
Negative color : R255, G255, B0

and applies when the cell is edited

 

[Hercules1946]

If this is supposed to be a built in feature rather than something you run when required, why not use Conditional Formatting ?

 

[zutcom]

I know how to program in file maker but not in excel where I can find the conditional formatting ?

 

[JLGWhiz]

Mumps had the solution, he just gave you more than you needed. Here is his code modified to only cell F5.

Sub test()
    Application.ScreenUpdating = False
    
        If Range("F5") >= 0 Then
            Range("F5").Interior.ColorIndex = 6
        ElseIf Range("F5") < 0 Then
            Range("F5").Interior.ColorIndex = 44
        End If
    
    Application.ScreenUpdating = True
End Sub

 

[JLGWhiz]

I think I would put it in a Worksheet_Change event so it would change colors automatically when you change the value in the cells.
To use this code, copy it to the Worksheet code module. To access the sheet code module, right click the sheet name tab, then click 'View Code' in the pop up menu. After you have installed the code, the color in cell F5 will change automatically as values go from positive to negative and vice versa. It will not change from sheet calculations which cause the value to change.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("F5")) Is Nothing Then
    Application.EnableEvents = False
    Application.ScreenUpdating = False
        If Range("F5") >= 0 Then
            Range("F5").Interior.Pattern = xlNone
            Range("F5").Interior.ColorIndex = 6
        ElseIf Range("F5") < 0 Then
            Range("F5").Interior.ColorIndex = 44
            Range("F5").Interior.Pattern = 15
            Range("F5").Interior.PatternColorIndex = 12
        End If
    Application.ScreenUpdating = True
    Application.EnableEvents = True
End If
End Sub

 

[Hercules1946]

I know how to program in file maker but not in excel where I can find the conditional formatting ?

Not sure what file maker is, but if you open Excel and you have 2010 or 2007 versions click the Home tab on the menu ribbon, and you'll find Conditional Formatting in
the Styles group. It has tools to set the colours of cell fonts and backgrounds based on the cell values as well as lots of other criteria. Basically you select the cells
you want to format and then build a suitable rule or rules to apply them. By the way, I thought initially that you talked about wanting to format in Col H but we now
seemed to have switched to cell F5 only ???