Excel - Change Cell Color based on Cell Value Divided by 1000 - Duel 166 - Episode 1928
2,210 views · Published 3 October 2014 · 11:40 · Indexed 6 November 2026
Channel: MrExcel.com · 2014 · Education
Microsoft Excel Tutorial: Apply Color by Thouands Welcome to another exciting episode of Dueling Excel, where Mike Girvin from Excel Is Fun and I, Bill Jelen from MrExcel, go head to head to find the best solution to your Excel problems. In this episode, we tackle a question from Melissa on YouTube, who wants to highlight rows based on the value of a cell. Specifically, she wants to highlight rows that fall within certain ranges, such as between 1000 and 2000, 2000 and 3000, and so on. My initial thought was to use Conditional Formatting, but with so many different ranges, it would be a hassle to set up multiple rules. So, I came up with a solution using VBA. By dividing the number in the cell by 1000 and using the integer function, we can determine which range the number falls into. Then, using a macro, we can assign different theme colors to each range, making use of the theme color trick. This way, every band of thousands gets a different color, making it easy to identify the range at a glance. But Mike had a different interpretation of the question. He thought that Melissa wanted to be able to input the ranges herself and have the corresponding rows highlighted. So, he came up with a solution using Conditional Formatting and formulas. By building the formula off to the side and copying it down, he was able to get the pattern of TRUEs and FALSEs to work. Then, by copying the formula into the Conditional Formatting dialog box, he was able to highlight the rows in the desired ranges. In the end, it wasn't just a Dueling Excel techniques, but also a Dueling interpretation of the question. This brings up a shameless product plug for my new website, excelarticles.com, where you can find interesting articles and discussions on hot topics from the MrExcel message board. So, if you're looking for some interesting reading on Excel, be sure to check it out. Thank you for tuning in to this episode of Dueling Excel. We hope you found our solutions helpful and we'll see you next time for another exciting episode. Don't forget to like, comment, and subscribe to our channel for more Excel tips and tricks. And as always, keep Excelling! Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/ You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/ Table of Contents: (00:00) Theme Music (00:36) Change Color Based on 1000-1999 and so on (00:51) Highlighting a row based on cell value divided by 1000 (01:03) Test formula for Conditional Formatting (01:17) Formula for determining bucket using INT() (02:17) Using VBA for Conditional Formatting (05:14) Adjusting color shades (06:13) Mike's solution using Conditional Formatting and formulas (10:30) Clicking Like really helps the algorithm #excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial #excelformula #conditionalformatting #conditionalformat This video answers these common search terms: how to color cells on excel from a list how to create coloured cells from input in excel how to conditional color format in excel how to conditional format colors in excel how to do a conditional color code in excel how to create a new rule conditional formatting excel colours how color a excel cell with a condition how to add color ranges to excel Melissa wants to apply a color to values in the 1000-1999, 2000-2999, and so on ranges. This Dueling Excel podcast comes down to a question of what did Melissa really mean by her question. Bill does a VBA Macro to color all bands. Mike does an input cell and conditional formatting to highlight one chosen band. Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1153754/
More from this channel
-
2:13
Excel Calculate Decimal Hours from two Time Cells - Episode 902
-
3:57
Excel 2007 - Color of Selected Sheets is too much like unselected sheets - Episode 907
-
2:48
Excel - Adding Equals Icon to Excel QAT - Build Formula with Mouse - Episode 911
-
3:30
Excel Grouping Percentages into Buckets for a Pivot Table - Method 2 - Episode 913
-
2:04
Excel Change Color of Selected Cells - Episode 914
-
4:38
Excel - Auto Numbering a List with Frequent Inserts & Deletes - Episode 916
-
2:00
Excel Hiding Data in Plain Site - Episode 919
-
2:20
Excel - Bring Back The Full Excel 2003 Dialogs In Excel! - Episode 920