Click here for important information about Computing.net.

Can Excel cells be identified by color?

Dell / Inspiron 17r
January 21, 2021 at 13:06:36
Specs: windows 10, 2.3/8
I have a spreadsheet that uses 12 rows (one for each month) and 31 columns (for each day of a month). Any specific date is the intersection of the appropriate month and day and that cell contains a value. Each day of the week has a different background color. I was able to sum the totals when the cells when in the same range, i.e, A1:A5. but not when the cells were not in the same range. Can I sum the values of cells by color if the cells are not in the same range, i.e., A3, B4, C5 , etc.?

Thank you.
Brian W

message edited by Brian W


See More: Can Excel cells be identified by color?


#1
January 21, 2021 at 14:25:03
Don't remember that much from excel, but you can make if conditions:
https://www.excel-easy.com/examples...

IF=(condition,true,false)

Or you could just add A3, B4 and C5 ?


Reply ↓  Report •

#2
January 21, 2021 at 16:21:34
What version of Excel are you on?
I believe that the newer versions, 2010 or 2016, has this ability, although I may be wrong.

How are the cells colored, Conditional Formatting or did you manually color them?

Can you post a small sample, because if you have twelve rows,
how does your A3 fit in with B4 & C5?

MIKE

http://www.skeptic.com/


Reply ↓  Report •

#3
January 21, 2021 at 17:59:41
Mike,

It’s Excel 2019. I have not used background colors as I wanted to know if this project is doable. I want to color each day of the week a different color, i,e., , Monday’s cells would be red, Tuesday’s green, etc. Each cell contains a value. I then want to find the sum of all red cells, all green cells, etc. The calendar has 12 rows and 124 columns. Each date has uses for four columns. For example, the column for January 1 has A1:A12, B1:B12, C1:C12 and D1:D12.

Thank you,
Brian W


Reply ↓  Report •

Related Solutions

#4
January 21, 2021 at 18:18:37
Your going to have to post an example, because I can not visualize

"12 rows (one for each month)"
and
"the column for January 1 has A1:A12, B1:B12, C1:C12 and D1:D12. "

In what column do you have the Months if not column A?

MIKE

http://www.skeptic.com/


Reply ↓  Report •

#5
January 21, 2021 at 18:43:43
Mike,

I’ll post an example tomorrow. What is the url for the site that will let me send you the spreadsheet?

Thanks,
Brian W


Reply ↓  Report •

#6
January 22, 2021 at 05:30:56
mmcconaghy,

I found file convoy:http://www.fileconvoy.com/dfl.php?id=ged81b8162f7ecac0100032366949fe5cc63eaf030f

I uploaded the entire file but only colored the data for January 1; I don't know if what I am asking is doable --- the sums of values of cells by color background.

Thank you,
Brian W


Reply ↓  Report •

#7
January 22, 2021 at 07:13:16
Brian,

It is difficult to tell what you are trying to do, but I'll just
ask: Instead of using the background color to determine
whether to add the value of a cell, could you use the
same formula that you use to determine the background
color? I can see that you might be trying to avoid that
because using the background color would be simpler
and more compact.

-- Jeff, in Minneapolis


Reply ↓  Report •

#8
January 22, 2021 at 07:23:06
Every solution I've found gets screwed up because of the use of the Letter-Colon in the cells. The various solutions use a simple SUM or SUMIF function based on the ColorIndex number of the cell. Trying to modify the solutions to convert the text strings into numbers first is cumbersome to say the least.

If you already have the letters in the column headings, why do you need them in the cells?

message edited by DerbyDad03


Reply ↓  Report •

#9
January 22, 2021 at 07:32:52
DerbyDad03,

Thank you for your response. Now that the columns are labeled, the letter-colon can be eliminated.

Brian W


Reply ↓  Report •

#10
January 22, 2021 at 07:34:11
Jeff,

Thank you for your response. The background colors of the cells were manually added and not by any formula.

Brian W


Reply ↓  Report •

#11
January 22, 2021 at 12:34:22
So I'm curious whether you manually set the colors 31 times, or
366 times, or what. With the right formula, you should only need
to do it seven times.

-- Jeff, in Minneapolis


Reply ↓  Report •

#12
January 22, 2021 at 13:31:52
Kilavila,

Thank you for your response.

Brian W


Reply ↓  Report •

#13
January 22, 2021 at 13:38:56
Jeff,

I have only put color backgrounds in the cells for January 1 as I was (am) unsure if this project is doable. Since I do not have a formula to do this, the color backgrounds were done manually, one color at a time.

Thank you.
Brian W


Reply ↓  Report •

#14
January 22, 2021 at 14:23:25
re: "Now that the columns are labeled, the letter-colon can be eliminated."

Based on the example sheet that you supplied, and replacing the letter-colon-number entries with just the numbers, your yellow cells SUM to 25.3 using a modified version of the method found here:

https://www.brainbell.com/tutorials...

I had to change the use of the Interior.ColorIndex property to Interior.Color
(That is because of expanded color pallet that Excel provides since the original code was written.)

My method:

1 - I pasted the code shown below into a VBA module. (That's the modified version)

2 - I entered this formula in C22 and dragged it to the right until O22:

=ColorFunction(C21,$U$3:$X$14,TRUE)

It returned 25.3 under the yellow FRI cell, zeros everywhere else.

3 - I entered various numbers in some of the other colored cells and watched the SUM's miraculously appear in Row 22.

4 - (Housekeeping) I cleaned up the sheet by deleting the formula from Row 22, Columns D, F, H, J, L & N

As noted at that website, if you use FALSE (or 0) instead of TRUE (or 1) the function will COUNT the number of cells that match the color of the day.

Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean)
'Example Formula for SUM: =ColorFunction(C21,$U$3:$X$14,TRUE)
Dim rCell As Range
Dim lCol As Long
Dim vResult
lCol = rColor.Interior.Color
    If SUM = True Then
       For Each rCell In rRange
        If rCell.Interior.Color = lCol Then
                vResult = WorksheetFunction.SUM(rCell) + vResult
        End If
       Next rCell
    Else
        For Each rCell In rRange
        If rCell.Interior.Color = lCol Then
                vResult = 1 + vResult
        End If
       Next rCell
End If
ColorFunction = vResult
End Function

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

message edited by DerbyDad03


Reply ↓  Report •

#15
January 23, 2021 at 10:59:42
DerbyDad03,

Thank you for your response. It initially worked; however, I had two problems:

1. U3 and X14 is the initial range of cells. When I changed the range to U3:EN14 and colored in the background of cells that contained values, the appropriate counter in Row 22 did not change;
2. When I change a background color, the appropriate counters do not change the sums.

Thank you,
Brian W

message edited by Brian W


Reply ↓  Report •

#16
January 23, 2021 at 19:04:25
Just like any standard, built-in Excel function (SUM, COUNT, VLOOKUP, whatever) the sheet must calculate for a User Defined Function (UDF) to change it's result. Choosing a Fill color does not cause the sheet to calculate because, in most cases - specifically with any built in function - the change of a fill color isn't going to change the result of a formula.

Excel is designed to only calculate when a change is made to a cell that some other cell is dependent upon. In other words, if you enter 5 in A1, and no other cell contains a formula that references A1, Excel doesn't bother to calculate. However, if B1 contains =SUM(A1:A2) then Excel will know that formula in B1 is dependent on A1 and will therefore calculate if you change the value in A1. If Excel calculated every time any change was made to any cell, it could suck up resources unnecessarily. See here for a deeper explanation:

http://www.decisionmodels.com/calcs...

However, interestingly enough, using the Format Painter does cause the UDF to recalculate. Don't ask me why, but it does. (I'm sure that if you asked the question at the Excel-G listserv site, somebody will know the answer. It might be a good way to introduce yourself)

So, instead of Filling the cell in your table by choosing a color from the Excel color pallet, select a cell that is already filled with the color you want, choose the Format Painter button on the Home tab and then select the cell(s) that you want to be the same color. Be aware that the Format Painter will paint all formats contained in the source cell, so make sure that the source cell is formatted the way you want the destination cell to be formatted.

FYI...if you double click the Format Painter button, then you can copy the format to multiple cells or ranges just by selecting them one after the other. Press escape to release the Format Painter.

message edited by DerbyDad03


Reply ↓  Report •

#17
January 24, 2021 at 06:02:17
DerbyDad03,

Thank you for your response. I'll give Format Painter a try. In a previous post, I mentioned that the prefix and the colon can be eliminated. I did that and then had to rewrite some of the formulas so that I would get the correct counts and sums. However, the formula used to find the average is not working correctly. 222.1/18 = 12.38. The 222.1 is the sum of values in cells U3:EK3 and 18 is the count of cells that contain those values. This formula returned 11.13. The formula I used was =IFERROR(SUM(U3:EK3)/COUNTA(U3:EK3),0). There are three different cells that show the totals of values, the counts and the average. Theses cells only show values for one month and should be the same; however, this “average “ shows 12.38, the correct answer. I could send you the spreadsheet via file convoy if you want t.

Thank you.
Brian W

message edited by Brian W


Reply ↓  Report •

#18
January 24, 2021 at 07:18:00
222.1/18 = 12.34, not 12.38.

Either you have a typo in your post or the SUM of your values is not 222.1.

In addition, did you apply COUNTA to your range of cells to see what it returns on it's own? Maybe there is something in a cell that you don't see and COUNTA is retuning something other than 18.

Finally, do you ever use the Evaluate Formula feature so that you can watch your formulas step through the calculations? It's a very powerful tool for debugging formulas because it will return each individual calculation, step by step.

In this case it'll show your SUM result, then your COUNTA result, then the result of the IFERROR.



Reply ↓  Report •

#19
January 24, 2021 at 08:49:36
DerbyDad03,

Thank you for your response. I tried Evaluate formula and found the error. The formula I using using calculated the sum of all the values in the range but I need the values in cells in every fourth cell i.e., U3, Y3, AC3, etc. I was thinking that I could change the range to the specific cells needed. Would that work?

In regard to the VBA module, does the range given in line 2 need to be changed to show the entire range of U3:EK3. If so, how it that done?
.

Brian W

message edited by Brian W


Reply ↓  Report •

#20
January 24, 2021 at 10:12:05
re: "I was thinking that I could change the range to the specific cells needed. Would that work?"

I don't know, it's your sheet. Try it.

re: "In regard to the VBA module, does the range given in line 2 need to be changed..."

Just to be clear as far as terminology, a VBA Module is similar to a folder in Windows. It is a place where code is stored.

When you ask about "Line 2", you are are really asking about Line 2 of a specific piece of code, in this case the UDF named ColorFunction. The module itself could contain multiple pieces of code, be they UDFs, Macros, etc.

In answer to your specific question, Line 2 is a Comment. Note the apostrophe at the start and the green font color. That means that it is not an instruction, just some information for the reader. In this case, it's just there to show you the format of the UDF that needs to be used in the cell.

The place where you need to change the range is in the UDF (the formula) that you entered into your cells. Note the variables contained in first line of the code. The value for those variables get passed to the code from the UDF in the cell.

In other words, within VBA the formula sort of looks like this:

=ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean)

When you use =ColorFunction(C21,$U$3:$X$14,TRUE) in a cell, VBA sees this:

rColor = C21
rRange = $U$3:$X$14
Optional SUM As Boolean = TRUE

Then, when the code actually runs, it uses those values each time it encounters those variables in an instruction.

re: A change to VBA code: "If so, how it that done?"

If it needed to be done (and it doesn't in this case) you would simply type the change into the code. As soon as you click outside of the line you are editing, the VBA editor would automatically compile the code and it would be ready to run. If the syntax was wrong, the font would turn red and the VBA editor would present you with an error message. You can't run the code until the error in fixed.



Reply ↓  Report •

#21
January 24, 2021 at 10:45:24
DerbyDad03,

Thank you for your response. I tried Format Painter and it worked as desired; the spreadsheet can now sum the values of cells by color.

I am somewhat confused about VBA modules. If I change the range in the cells, the UDF will automatically recognize these changes without making any manual changes in the VBA. Is this correct?

I am sorry for all of these posts but I want to get the spreadsheet working correctly before Computing.net goes off-line.

Thanks for all of your assistance over the years that I have used Computing.net.

Brian W


Reply ↓  Report •

#22
January 24, 2021 at 16:29:21
re: "If I change the range in the cells, the UDF will automatically recognize these changes without making any manual changes in the VBA. Is this correct?"

There should be no need to edit the code. I tried to explain that in my previous post. Re-read the part about the formula passing the range arguments to the code.

Of course, you could have simply tried it to see. ;-)

message edited by DerbyDad03


Reply ↓  Report •

#23
January 24, 2021 at 16:44:47
DerbyDad03,

Thank you for your response: I did try it and it didn't work so I probably made an error. I am having trouble with the summing by color. I inserted the script into the VBA, and there was a message that said "The following features cannot be save in macro-free workbooks. To save with these features, click NO and then choose a macro-enable file type in the File Type list. To continue saving as a macro-free workbook, click Yes." I clicked YES, and I put values in the cells that had different colors and it worked correctly. I saved and closed the spreadsheet and when I tried again it didn't work. I reinserted the script and this time I selected NO and saved it as an Excel macro-enabled workbook. It worked correctly once again but when I saved and closed the file again, it did not work when I reopened the file. What am I doing wrong?

Thank yoy.
Brian W


Reply ↓  Report •

#24
January 24, 2021 at 18:03:21
I have no idea what you did wrong. It obviously won't work if you save it as a macro free workbook, so assuming you saved it as a .xlsm or .xlsb file, it should work.

Did you check to see that the code was still there?

message edited by DerbyDad03


Reply ↓  Report •

#25
January 25, 2021 at 05:22:27
DerbyDad03,

Thank you for your response. Finally, after hours of trials, I was able to get everything to work correctly. In order to do this, it was necessary to change the range in C21 to include the entire range of the spreadsheet.

I have manually used Format Painter to color in the background of selected cells. Given selected cells, is there a way that this process can be automated by a formula?

Thanks again,
Brian W

message edited by Brian W


Reply ↓  Report •

#26
January 25, 2021 at 07:15:37
re: "it was necessary to change the range in C21..."

What "range in C21"? In the workbook that you supplied C21 contains the text SUN. There is no "range" in C21.

"...to include the entire range of the spreadsheet."

The entire range of the spreadsheet, at least in my version of Excel, is A1:XFD1048576. That's over 17 billion cells. I can't think of one possible reason that you would need to reference the entire sheet to get the UDF to work.


Reply ↓  Report •

#27
January 25, 2021 at 07:38:39
DerbyDad03,

Thank you for your response. I do not know enough about VBA and UDF to make a valid comments about its coding or usage. When I used =ColorFunction(C21, $U$3: $X$14, True), it worked for values in that range but not for any values outside of that range. When I changed the formula to =ColorFunction(C21, $U$3: $EN$14, True), it worked correctly for the entire spreadsheet.

Thanks again,
Brian W


Reply ↓  Report •

#28
January 25, 2021 at 10:02:12
This has nothing to do with VBA, UDFs or coding. It has everything to do with what you said in post #25.

You said: "In order to do this, it was necessary to change the range in C21 to include the entire range of the spreadsheet."

First, as I said in Post #26:

In the workbook that you provided the link to, C21 contains text, specifically SUN. There is no range to change in C21, at least not in the workbook that you provided. The following image shows the workbook that you provided. Note the contents of C21. How did you "change the range" in that cell?

https://i.imgur.com/KNQPSvn.jpg

Perhaps you mean that you changed the range in C22, which is where the ColorFunction formula should be placed. Or perhaps you changed the worksheet such that C21 now contains the ColorFunction formula. Either way, we can only work with what you tell us and telling us that you changed the range in C21 just doesn't make any sense based on the workbook that you provided.

Second: When you use the phrase "entire spreadsheet" you confuse matters even more. To me, and most other Excel users, the phrase "entire spreadsheet" means every cell in every row and every column.

What I assume you mean is this:

"When I changed the formula to =ColorFunction(C21, $U$3: $EN$14, True), it worked correctly for my entire table."


Reply ↓  Report •

#29
January 25, 2021 at 14:47:19
DerbyDad03,

Thank you for your response. I changed the formula in C22 and the other cells in that same row that were associated with different colors. Everything is working correctly. I manually used Format Painter to add the appropriate color background to the entire table.

Thanks again for your assistance.
Brian W


Reply ↓  Report •

Ask Question