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

Watch on YouTube

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