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.
