H
hovendick
can anybody tell me how to fill a cell with color red if
the value is negative and no fill if it is positive?
thanks
the value is negative and no fill if it is positive?
thanks
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
J
JulieD
Hi
Use Conditional formatting for this
select the cell(s) you want this to happen in
format / conditional formatting
cell value is less than 0
click on the format button choose the patterns tab, choose red
click OK & OK
Regards
JulieD
Use Conditional formatting for this
select the cell(s) you want this to happen in
format / conditional formatting
cell value is less than 0
click on the format button choose the patterns tab, choose red
click OK & OK
Regards
JulieD
E
ElsiePOA
Rather than using an IF statement, I suggest you use conditiona
formatting. Highlight the cells you want to format and select Format
Conditional Formatting.
In the left hand, box select "Cell Value Is".
In the center box, select "Less Than"
In the right box, enter 0
Then, clickon Format, select Patterns, click the red square and clic
OK.
That should do it
formatting. Highlight the cells you want to format and select Format
Conditional Formatting.
In the left hand, box select "Cell Value Is".
In the center box, select "Less Than"
In the right box, enter 0
Then, clickon Format, select Patterns, click the red square and clic
OK.
That should do it
E
ElsiePOA
Rather than using an IF statement, I suggest you use conditiona
formatting. Highlight the cells you want to format and select Format
Conditional Formatting.
In the left hand, box select "Cell Value Is".
In the center box, select "Less Than"
In the right box, enter 0
Then, clickon Format, select Patterns, click the red square and clic
OK.
That should do it
formatting. Highlight the cells you want to format and select Format
Conditional Formatting.
In the left hand, box select "Cell Value Is".
In the center box, select "Less Than"
In the right box, enter 0
Then, clickon Format, select Patterns, click the red square and clic
OK.
That should do it
P
Peo Sjoblom
Format>conditional formatting, cell value is less than 0, format button and
select red (font or pattern)
click OK twice
--
Regards,
Peo Sjoblom
(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)
select red (font or pattern)
click OK twice
--
Regards,
Peo Sjoblom
(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)
E
ElsiePOA
An additional thought -
If you want to improve the contrast in the cells you format to red, yo
can while doing the conditional format, also select Font and change th
color to white.
That way, numbers equal to or larger than 0 will black font in whit
cells while negative numbers will be white font in red cell
If you want to improve the contrast in the cells you format to red, yo
can while doing the conditional format, also select Font and change th
color to white.
That way, numbers equal to or larger than 0 will black font in whit
cells while negative numbers will be white font in red cell
C
Cheif
I'm assuming you're working a column of numbers. There's an easy way to do
this with a macro. The only thing I don't know is, what is at the end of
each of your columns. We'll need some way to stop the macro. To do this,
I've entered the word 'end' at the ending of my test column.
Another thing I don't know is whether you've ever used macros. The macro
below is very basic. The cursor must be manually placed at the beginning of
each column you want to check for changing cell backgrounds (patterns) to red
and then be activated.
I suggest you copy a column of your numbers to a test spreadsheet. Place
the word 'end' below the last columnar entry.
(1), Click on 'Tools', 'Macro', 'Record New Macro'. The Record Macro msgbox
will appear with some selections you can make. (a), 'Shortcut key'. Type the
letter 'a'. (b), 'Store macro in'. Scroll and select, 'This workbook'.
Click the 'OK' button
The macro recorder tool bar will appear at the bottom of your monitor.
Click the little blue square at the left to stop recording.
(2), Click on 'Tools', 'Macros'. An msgbox will appear. Select the macro
name, i.e., Macro1. Click on 'Edit'. The macro recorder/editor appears with
the macros's name, and some other info, namely green colored apsotrophes.
(3), Use the delete key to delete the green apostrophes with nothing that
follows them.
(4), Copy the eight macro lines below to your clipboard. Paste them between
the last green entry and above the words 'End Sub".
ActiveCell.Select
Do Until ActiveCell.Value = "end"
If ActiveCell.Value < 0 Then
With Selection.Interior
. Color Index = 3
. Pattern = xlSolid
End With
End If
Selection.Offset (1, 0).Select
Loop
If ActiveCell = "end" Then
End If
(5), Click on 'File'. Click on 'Close and Return to Microsoft Excel'
Place the cursor over the first entry in your copied column. Press <CTRL>A.
All of your negative numbers in the column have been machine identified and
their cell backgrounds colored red--eliminating human error.
This has been a quick lesson using a simple macro to do something frequently
done in the spreadsheet world. It may have convinced you to learn to record
macros using the recorder--if you've never written or recorded a macro. It's
a good way to start learning. If you can use this macro, but you can't use
the word 'end', post a reply telling us what is below the end of each of your
columnar entries. Undoubtedly we can use that to stop the macro.
this with a macro. The only thing I don't know is, what is at the end of
each of your columns. We'll need some way to stop the macro. To do this,
I've entered the word 'end' at the ending of my test column.
Another thing I don't know is whether you've ever used macros. The macro
below is very basic. The cursor must be manually placed at the beginning of
each column you want to check for changing cell backgrounds (patterns) to red
and then be activated.
I suggest you copy a column of your numbers to a test spreadsheet. Place
the word 'end' below the last columnar entry.
(1), Click on 'Tools', 'Macro', 'Record New Macro'. The Record Macro msgbox
will appear with some selections you can make. (a), 'Shortcut key'. Type the
letter 'a'. (b), 'Store macro in'. Scroll and select, 'This workbook'.
Click the 'OK' button
The macro recorder tool bar will appear at the bottom of your monitor.
Click the little blue square at the left to stop recording.
(2), Click on 'Tools', 'Macros'. An msgbox will appear. Select the macro
name, i.e., Macro1. Click on 'Edit'. The macro recorder/editor appears with
the macros's name, and some other info, namely green colored apsotrophes.
(3), Use the delete key to delete the green apostrophes with nothing that
follows them.
(4), Copy the eight macro lines below to your clipboard. Paste them between
the last green entry and above the words 'End Sub".
ActiveCell.Select
Do Until ActiveCell.Value = "end"
If ActiveCell.Value < 0 Then
With Selection.Interior
. Color Index = 3
. Pattern = xlSolid
End With
End If
Selection.Offset (1, 0).Select
Loop
If ActiveCell = "end" Then
End If
(5), Click on 'File'. Click on 'Close and Return to Microsoft Excel'
Place the cursor over the first entry in your copied column. Press <CTRL>A.
All of your negative numbers in the column have been machine identified and
their cell backgrounds colored red--eliminating human error.
This has been a quick lesson using a simple macro to do something frequently
done in the spreadsheet world. It may have convinced you to learn to record
macros using the recorder--if you've never written or recorded a macro. It's
a good way to start learning. If you can use this macro, but you can't use
the word 'end', post a reply telling us what is below the end of each of your
columnar entries. Undoubtedly we can use that to stop the macro.
C
Cheif
I copied my first macro incorretly. I apologize. This one should work with directions in my first post.
ActiveCell.Select
Do Until ActiveCell = "end"
If ActiveCell.Value < 0 Then
With Selection.Interior
.ColorIndex = 3
.PatternColorIndex = xlSolid
End With
End If
Selection.Offset (1, 0). Select
Loop
If ActiveCell = "end" Then
End If
ActiveCell.Select
Do Until ActiveCell = "end"
If ActiveCell.Value < 0 Then
With Selection.Interior
.ColorIndex = 3
.PatternColorIndex = xlSolid
End With
End If
Selection.Offset (1, 0). Select
Loop
If ActiveCell = "end" Then
End If