To post as a guest, your comment is unpublished.· 3 months agoHello,
You can select the formula cells, and then apply the Conditional Formatting > Greater than (0)-format it green, and then Less Than (0)-format it red, see screenshot:
Please try it, hope it can help you, thank you!
How to change background color based on cell value in Excel?
When we deal with huge data in Excel, we may want to pick out some specific data and highlight them, such as change their background color. This article is talking about how to change the background color based on cell values in Excel quickly.
Select and change background color based on cell value:
Kutools for Excel’s Select Specific Cells feature can help you find and select the specific cells, and then you can do some formatting for the selected cells you need.
Kutools for Excel: with more than 200 handy Excel add-ins, free to try with no limitation in 60 days. Download and free trial Now!
Supposing you have a range of data, and now you need to color the values between 80 and 100, as the following screenshot shown, how could you change background color based on cell value?
Excel's Conditional Formatting command can change background color for specific cells based on their values with following steps:
1. Select the range that you will work with, then click the Home > Conditional Formatting > New Rule….
2. In the New Formatting Rule dialog box, select and highlight the Format only cells that contain item in the Select a Rule Type: box, and in the Format Only Cells with section, specify the conditions that you need.
- In the first drop down box, select the Cell Value;
- In the second drop down box, select the criteria:between;
- In the third and fourth box, enter the filter conditions, such as 80, 100.
3. Click the Format button, in the Format Cells dialog box, set the background color under Fill tab.
4. Click OK > OK to close the dialogs. Then the specific cells with value between 80 and 100 are filled with the new background color in the selection. See screenshot:
Note: The Conditional Formatting is a dynamic feature, the cell color will be changed as the data changes.
Except the Conditional Formatting, here, we bring you another easy way: Kutools for Excel's Select Specific Cells tool, which can help you select the specific cells firstly, and fill them later.
|Kutools for Excel : with more than 120 handy Excel add-ins, free to try with no limitation in 60 days.|
After Installing Kutools for Excel, please do as follows:
1. Select the range that you will work with, and click the Kutools > Select > Select Specific Cells….
2. In the Select Specific Cells dialog box, check the Cell option; specify the filter condition from Specific type, and then click OK button. See the following screenshot:
3. Then cells containing values between 80 and 100 are selected in the selection. Go on to click the Fill button under Home tab to change their background color. See screenshot:
This Select Specific Cells utility also can help you to select and color the specific text or value as you need. Please do as follows:
1. Select the data range that you want to color the specific cells.
2. Then apply this Select Specific Cells feature, in the popped out dialog box, click Cell under the Selection type, and choose Equals from the Specific type drop down list, then enter the specific text or value that you want to select. See screenshot:
3. Then click Ok button, and the specific values in the selection have been selected, and then specify a color under the Fill Color button to shade the cells you need, see screenshot:
Recommended Productivity Tools
Bring handy tabs to Excel and other Office software, just like Chrome, Firefox and new Internet Explorer.
Amazing! Increase your productivity in 5 minutes. Don't need any special skills, save two hours every day!
200 New Features for Excel, Make Excel Much Easy and Powerful:
- Merge Cell/Rows/Columns without Losing Data.
- Combine and Consolidate Multiple Sheets and Workbooks.
- Compare Ranges, Copy Multiple Ranges, Convert Text to Date, Unit and Currency Conversion.
- Count by Colors, Paging Subtotals, Advanced Sort and Super Filter,
- More Select/Insert/Delete/Text/Format/Link/Comment/Workbooks/Worksheets Tools...
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
To post as a guest, your comment is unpublished.· 3 months agoSir, I have value in cell A10, in A12. Now I entered a simple formula in A15 subtracting these (A10-A12). I want background color of result cell (A15) in green if A10 is large (means result in positive number) and background color in red if A12 is large ( result in negative number). Please provide solution
To post as a guest, your comment is unpublished.· 4 months agoIf I have a date in a cell, how do I make it change to orange when it is within 30 days of coming due, yellow when it is within 15 days of coming due and red when it exceeds the date (past due)
To post as a guest, your comment is unpublished.· 8 months agoI thought this color of cells by value was going to be a real pain. It could not have been explained more easily and clearly. Thanks. It took no less than 1-2 minutes to understand what to do.
To post as a guest, your comment is unpublished.· 1 years agoHI,
I want if i change any value or number in any cell then that cell text color should get change for e.g. if number in any cell is 100 and I have to change it as 98 then that cell text 98 should change into red color. Is it possible in Excel?? Do clarify please
To post as a guest, your comment is unpublished.· 1 years agoSo I am creating a excel spreadsheet for fire department reports. What I am looking for is it to calculate the days a report is overdue, unless it has been completed:
Column A Column B Column C Column D Column E Column F
Incident # Incident Date Todays Date Completed Date Days Overdue Todays Date
I want column B subtracting Column C to calculate in column E unless Column D is complete. I hope this makes sense. I want to use the conditional formatting to show me in colors how long a report is overdue (with the 3 color scale). I have the formula for the number of days it is overdue to have a correct value and color scale, but I want it to remain green when the report has been completed.
- ← Previous
- Next →