need in macro

zutcom

New Member
Joined
Dec 15, 2013
Messages
3
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
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Hi Z. Assuming your values are in column H, try this macro:
Code:
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
 
Upvote 0
Hi Z. Assuming your values are in column H, try this macro:
Code:
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
 
Upvote 0
If this is supposed to be a built in feature rather than something you run when required, why not use Conditional Formatting ?
 
Upvote 0
I know how to program in file maker but not in excel where I can find the conditional formatting ?
 
Upvote 0
Mumps had the solution, he just gave you more than you needed. Here is his code modified to only cell F5.
Code:
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
 
Upvote 0
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.
Code:
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
 
Upvote 0
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 ???
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,108
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top