Telling red cells from green cells in Excel is difficult for those suffering from color blindness. In Episode 771, I take a look at a macro that will show the text name of the color in the status bar for any selected cell.
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Hey. Welcome back to the MrExcel netcast. I’m Bill Jelen.
Boy, I got a tough question in a seminar.
Someone said, hey, I'm color blind.
I can't distinguish between red and green, and when someone sends me a worksheet and tells me I'm supposed to work on all the green cells, I don't know how to do that.
He said he thought there used to be something where you could hover over a cell and see what color it is.
Now, I don't recall anything like that and I don't know where that setting is.
The one thing I do know is that if you open the Paint Bucket icon -- and this is in Excel 2003 and earlier -- there's a row of dots at the top, and we can take that row of dots and drag it above the spreadsheet, and you could, I guess, sort of memorize where in that array the red and the green are.
So, you know, the red are here, here, greens are kind of in this row, and then, when you select a cell, you can tell what cell is highlighted in that area, and so you can kind of tell that way whether it's on the red side or the green side.
Now, I’m not sure that that's necessarily the best choice.
Now, I said in the podcast I'd do a little bit of VBA every once a while and this seemed like a perfect situation for some VBA.
So, we'll switch over to the VBA editor here and you'll notice that the code is not sitting in module one or anything like that.
It's actually sitting right on the individual worksheet, and, basically, I was interested in the worksheet selection change.
So, to build this, from the left dropdown, choose WORKSHEET, from the right dropdown, choose SELECTIONCHANGE, and my concept is that, as we enter each cell, it would be nice down in the status bar to put the color of both the fill and the font color, and so, here, I set up a couple of variables.
Font color is TARGET.FONT.COLORINDEX, fill color, TARGET.INTERIOR.COLORINDEX, and then I went through the steps of talking about all the different possible colors.
You SELECT CASE FONTCOLOR, and then, you know, for example, if it's 8 or 28, that's basically AQUA.
If it's 5, 11, 20, 23, those are all variations of blue.
The one you have to watch out for is the no font color applied.
That’s a –4105 and so I call that BLACK.
Lots of lines of code here to figure out both the font color and the fill color, and then, at the very end, I use APPLICATION.STATUSBAR=, and I put a message down there.
So, let's take a look at how this works.
I'll insert some extra rows in the spreadsheet so that way our colors are down around the area where we can see the status bar.
Okay.
So, here, I chose one of the orange cells.
It says that the font is black, the fill is orange.
If I would choose another cell and change to a font color, so I'll go to one of the blue fonts, here it says font = number 11, that's blue, fill = 43 green.
Now, I realize, in the old Excel, we're living with a palette of 56 colors.
I realize that some people are smart enough to change their colors.
This assumes…those words assume that you're working in a worksheet where no one has changed the colors.
Certainly kind of a hassle here.
There really doesn't seem to be any good scenario to solve the problem for people who are color blind.
Now, if you're watching this podcast and you've run into this and you have a solution, please feel free to write me a note, bill@mrexcel.com, and let me know how you're dealing with the reds and greens in Microsoft Excel.
Well, thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
Boy, I got a tough question in a seminar.
Someone said, hey, I'm color blind.
I can't distinguish between red and green, and when someone sends me a worksheet and tells me I'm supposed to work on all the green cells, I don't know how to do that.
He said he thought there used to be something where you could hover over a cell and see what color it is.
Now, I don't recall anything like that and I don't know where that setting is.
The one thing I do know is that if you open the Paint Bucket icon -- and this is in Excel 2003 and earlier -- there's a row of dots at the top, and we can take that row of dots and drag it above the spreadsheet, and you could, I guess, sort of memorize where in that array the red and the green are.
So, you know, the red are here, here, greens are kind of in this row, and then, when you select a cell, you can tell what cell is highlighted in that area, and so you can kind of tell that way whether it's on the red side or the green side.
Now, I’m not sure that that's necessarily the best choice.
Now, I said in the podcast I'd do a little bit of VBA every once a while and this seemed like a perfect situation for some VBA.
So, we'll switch over to the VBA editor here and you'll notice that the code is not sitting in module one or anything like that.
It's actually sitting right on the individual worksheet, and, basically, I was interested in the worksheet selection change.
So, to build this, from the left dropdown, choose WORKSHEET, from the right dropdown, choose SELECTIONCHANGE, and my concept is that, as we enter each cell, it would be nice down in the status bar to put the color of both the fill and the font color, and so, here, I set up a couple of variables.
Font color is TARGET.FONT.COLORINDEX, fill color, TARGET.INTERIOR.COLORINDEX, and then I went through the steps of talking about all the different possible colors.
You SELECT CASE FONTCOLOR, and then, you know, for example, if it's 8 or 28, that's basically AQUA.
If it's 5, 11, 20, 23, those are all variations of blue.
The one you have to watch out for is the no font color applied.
That’s a –4105 and so I call that BLACK.
Lots of lines of code here to figure out both the font color and the fill color, and then, at the very end, I use APPLICATION.STATUSBAR=, and I put a message down there.
So, let's take a look at how this works.
I'll insert some extra rows in the spreadsheet so that way our colors are down around the area where we can see the status bar.
Okay.
So, here, I chose one of the orange cells.
It says that the font is black, the fill is orange.
If I would choose another cell and change to a font color, so I'll go to one of the blue fonts, here it says font = number 11, that's blue, fill = 43 green.
Now, I realize, in the old Excel, we're living with a palette of 56 colors.
I realize that some people are smart enough to change their colors.
This assumes…those words assume that you're working in a worksheet where no one has changed the colors.
Certainly kind of a hassle here.
There really doesn't seem to be any good scenario to solve the problem for people who are color blind.
Now, if you're watching this podcast and you've run into this and you have a solution, please feel free to write me a note, bill@mrexcel.com, and let me know how you're dealing with the reds and greens in Microsoft Excel.
Well, thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
