All solutions SUM Expert Solution – Excel SUM Problems

Excel - SUM Function Problem - Expert Solution

Question description:
This user has given permission to use the problem statement for this blog.

I am trying to have a tab change color when the Sum of a row = 0. I am using code on the tab but it appears to be confused because the value of the given cell is not 0 it is "=SUM(G4:G17)"
Solved by S. F. in 41 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
Excelchat Expert 01/03/2018 - 08:12
Hi welcome to Gotit
Excelchat Expert 01/03/2018 - 08:13
Hello are you there?
User 01/03/2018 - 08:14
I am here
Excelchat Expert 01/03/2018 - 08:14
Please share your sheet
Excelchat Expert 01/03/2018 - 08:14
I will solve for you
User 01/03/2018 - 08:15
sORRY i HAD TO WIPE SOME NAMES OFF IT FIRST
User 01/03/2018 - 08:15
And turn off my caps lock. Sorry'
Excelchat Expert 01/03/2018 - 08:16
Is it possible to share or not?
User 01/03/2018 - 08:17
for some reason it is not allowing me to upload the sheet.
User 01/03/2018 - 08:18
Could it be because it has active content?
Excelchat Expert 01/03/2018 - 08:18
Or else I will make the formula using example data
Excelchat Expert 01/03/2018 - 08:18
Is that fine?
User 01/03/2018 - 08:19
here is the code I put in the teb
User 01/03/2018 - 08:19
Private Sub Worksheet_Change(ByVal Target As Range) 'Updateby Extendoffice 20160930 If Target.Address = "$G$31" Then Select Case Target.Value Case "True" Me.Tab.Color = vbGreen Case Else Me.Tab.Color = vbRed End Select End If End Sub
User 01/03/2018 - 08:20
Cell G31 is a the sum of the column
Excelchat Expert 01/03/2018 - 08:20
We can use conditional formatting
User 01/03/2018 - 08:20
Should I remove the code?
Excelchat Expert 01/03/2018 - 08:21
IF sum is zero it should show red
Excelchat Expert 01/03/2018 - 08:21
or else it should show green right?
User 01/03/2018 - 08:22
I am looking to have it turn green when it equals 0 or else stay red
Excelchat Expert 01/03/2018 - 08:22
ok
Excelchat Expert 01/03/2018 - 08:22
if it is less than 0?
Excelchat Expert 01/03/2018 - 08:22
red only?
User 01/03/2018 - 08:23
equal to or less than zero is green. All other values would be red
Excelchat Expert 01/03/2018 - 08:23
ok
Excelchat Expert 01/03/2018 - 08:24
Check this
[Uploaded an Excel file]
Excelchat Expert 01/03/2018 - 08:25
A5 is sum of A1,A2,A3,A4
User 01/03/2018 - 08:25
I am looking to have where it says Sheet1 change color
Excelchat Expert 01/03/2018 - 08:26
if A5 is less than or equal to zero A5 becomes green
Excelchat Expert 01/03/2018 - 08:26
else sum of A5>0 it becomes red
Excelchat Expert 01/03/2018 - 08:26
Did you check my sheet?
User 01/03/2018 - 08:26
Right. That works for the cell but I am looking to get the Tab at the bottom (where it says Sheet1) change color
Excelchat Expert 01/03/2018 - 08:28
It requires Macro
User 01/03/2018 - 08:28
The code I put in seems to work if I manually enter a number into the target cell. But if the target cell has a function it seems to read the function.
User 01/03/2018 - 08:28
Is there a way to say if cell G31 = 0 then make cellG32 = 0
Excelchat Expert 01/03/2018 - 08:29
yes
User 01/03/2018 - 08:29
That way there is no function in cell G32
User 01/03/2018 - 08:29
If I can do that I think what I have will work
Excelchat Expert 01/03/2018 - 08:30
without function in G32 we have to make it 0 you mean?
User 01/03/2018 - 08:30
Without putting a function in G32 I need it to equal Cell G31
User 01/03/2018 - 08:30
I need to push a value into a cell
Excelchat Expert 01/03/2018 - 08:34
I will check your code
User 01/03/2018 - 08:35
I think the code I gave you says 'True' I have it changed to 0 on my code
Excelchat Expert 01/03/2018 - 08:51
Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$A$5" Then Select Case Target.Value Case "True" Me.Tab.Color = vbGreen Case "False" Me.Tab.Color = vbRed End Select End If End Sub
Excelchat Expert 01/03/2018 - 08:52
use this formula
Excelchat Expert 01/03/2018 - 08:52
And in target cell
Excelchat Expert 01/03/2018 - 08:53
write this formula
Excelchat Expert 01/03/2018 - 08:53
=IF(SUM(A1:A5)=0,TRUE,FALSE)
Excelchat Expert 01/03/2018 - 08:53
Are you there?
Excelchat Expert 01/03/2018 - 08:54
Hello

This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user information.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.

Click here to get your free Excelchat help session

Subscribe to Excelchat.co
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc