A complex VBA question
 

I have been attempting something for some time now and have been
unsuccesfull, maybe it is not possible, but if anyone has any ideas, please
let me know. I would like to be able to select a cell, press a push button
(there would be one for everyday of the week), that cell would then be
highlighted a specific color depending on what day button was pushed (a
different color for each day) at the same time a value from that row that the
cell was selected from would be added to a running total for each day. I
will try and describe it further this way:

abcdef 3543 8
ghijklm 4543 4
nopqrs 4564 6
tuvwxy 2342 5

I would like to select the 4543 cell, press a tuesday button, the cell would
then be highlighted the color i have selected for tuesday, and 4 would be
added to a column outside of the table to maintain a running total. I could
then select 2342 and select tuesday, the cell would be highlighted the same
color as 4543, and the running total would now equal 9. This seems difficult
and may not be possible, any ideas on how to make this work? thank you so
much.

A complex VBA question
 

You need this in the code for each button, see comments and adjust/ask
questions as needed
Private Sub CommandButton1_Click()
ActiveCell.Interior.ColorIndex = 4
Cells(1, 9) = Cells(1, 9) + Cells(ActiveCell.Row, 3)
' the last 3 assumes your number to add is in column 3
'this assumes I1 has your total, change to suit
End Sub
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"Josh Johansen" wrote:

I have been attempting something for some time now and have been
unsuccesfull, maybe it is not possible, but if anyone has any ideas, please
let me know. I would like to be able to select a cell, press a push button
(there would be one for everyday of the week), that cell would then be
highlighted a specific color depending on what day button was pushed (a
different color for each day) at the same time a value from that row that the
cell was selected from would be added to a running total for each day. I
will try and describe it further this way:

abcdef 3543 8
ghijklm 4543 4
nopqrs 4564 6
tuvwxy 2342 5

I would like to select the 4543 cell, press a tuesday button, the cell would
then be highlighted the color i have selected for tuesday, and 4 would be
added to a column outside of the table to maintain a running total. I could
then select 2342 and select tuesday, the cell would be highlighted the same
color as 4543, and the running total would now equal 9. This seems difficult
and may not be possible, any ideas on how to make this work? thank you so
much.

A complex VBA question
 

Thanks so much, I am going to give this a try now, is there a way to
determine what the colorindex is for the 5 colors i want to use for each day?
I will let you know how it goes once I put it in. Thanks!

"John Bundy" wrote:

You need this in the code for each button, see comments and adjust/ask
questions as needed
Private Sub CommandButton1_Click()
ActiveCell.Interior.ColorIndex = 4
Cells(1, 9) = Cells(1, 9) + Cells(ActiveCell.Row, 3)
' the last 3 assumes your number to add is in column 3
'this assumes I1 has your total, change to suit
End Sub
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"Josh Johansen" wrote:

I have been attempting something for some time now and have been
unsuccesfull, maybe it is not possible, but if anyone has any ideas, please
let me know. I would like to be able to select a cell, press a push button
(there would be one for everyday of the week), that cell would then be
highlighted a specific color depending on what day button was pushed (a
different color for each day) at the same time a value from that row that the
cell was selected from would be added to a running total for each day. I
will try and describe it further this way:

abcdef 3543 8
ghijklm 4543 4
nopqrs 4564 6
tuvwxy 2342 5

I would like to select the 4543 cell, press a tuesday button, the cell would
then be highlighted the color i have selected for tuesday, and 4 would be
added to a column outside of the table to maintain a running total. I could
then select 2342 and select tuesday, the cell would be highlighted the same
color as 4543, and the running total would now equal 9. This seems difficult
and may not be possible, any ideas on how to make this work? thank you so
much.

A complex VBA question
 

on a seperate page or different book paste and run this
Sub main()
For i = 1 To 36
Cells(i, 1).Interior.ColorIndex = i
Next
End Sub

will display all of the colors, the row number will be the color number you
want, there are ways to get many more if these are not quite right
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"Josh Johansen" wrote:

Thanks so much, I am going to give this a try now, is there a way to
determine what the colorindex is for the 5 colors i want to use for each day?
I will let you know how it goes once I put it in. Thanks!

"John Bundy" wrote:

You need this in the code for each button, see comments and adjust/ask
questions as needed
Private Sub CommandButton1_Click()
ActiveCell.Interior.ColorIndex = 4
Cells(1, 9) = Cells(1, 9) + Cells(ActiveCell.Row, 3)
' the last 3 assumes your number to add is in column 3
'this assumes I1 has your total, change to suit
End Sub
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"Josh Johansen" wrote:

I have been attempting something for some time now and have been
unsuccesfull, maybe it is not possible, but if anyone has any ideas, please
let me know. I would like to be able to select a cell, press a push button
(there would be one for everyday of the week), that cell would then be
highlighted a specific color depending on what day button was pushed (a
different color for each day) at the same time a value from that row that the
cell was selected from would be added to a running total for each day. I
will try and describe it further this way:

abcdef 3543 8
ghijklm 4543 4
nopqrs 4564 6
tuvwxy 2342 5

I would like to select the 4543 cell, press a tuesday button, the cell would
then be highlighted the color i have selected for tuesday, and 4 would be
added to a column outside of the table to maintain a running total. I could
then select 2342 and select tuesday, the cell would be highlighted the same
color as 4543, and the running total would now equal 9. This seems difficult
and may not be possible, any ideas on how to make this work? thank you so
much.

A complex VBA question
 

sorry i can go to 56 not just 36
Sub main()
For i = 1 To 56
Cells(i, 1).Interior.ColorIndex = i
Next
End Sub

--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"Josh Johansen" wrote:

Thanks so much, I am going to give this a try now, is there a way to
determine what the colorindex is for the 5 colors i want to use for each day?
I will let you know how it goes once I put it in. Thanks!

"John Bundy" wrote:

You need this in the code for each button, see comments and adjust/ask
questions as needed
Private Sub CommandButton1_Click()
ActiveCell.Interior.ColorIndex = 4
Cells(1, 9) = Cells(1, 9) + Cells(ActiveCell.Row, 3)
' the last 3 assumes your number to add is in column 3
'this assumes I1 has your total, change to suit
End Sub
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"Josh Johansen" wrote:

I have been attempting something for some time now and have been
unsuccesfull, maybe it is not possible, but if anyone has any ideas, please
let me know. I would like to be able to select a cell, press a push button
(there would be one for everyday of the week), that cell would then be
highlighted a specific color depending on what day button was pushed (a
different color for each day) at the same time a value from that row that the
cell was selected from would be added to a running total for each day. I
will try and describe it further this way:

abcdef 3543 8
ghijklm 4543 4
nopqrs 4564 6
tuvwxy 2342 5

I would like to select the 4543 cell, press a tuesday button, the cell would
then be highlighted the color i have selected for tuesday, and 4 would be
added to a column outside of the table to maintain a running total. I could
then select 2342 and select tuesday, the cell would be highlighted the same
color as 4543, and the running total would now equal 9. This seems difficult
and may not be possible, any ideas on how to make this work? thank you so
much.

A complex VBA question
 

John this is working out really well, I really appreciate it. I have found
that it would be more accurate that instead of taking one column and adding
it to the total, that I multiply two previous columns together and add that
product to the total (the column I am adding right now is supposed to be the
product, but there are errors in the data queried in) Does that make sense?
in my previous example I said I wanted to select 4543 and have 4 added, which
is working well, but there are two columns before the 4 that are 2 and 2 or 4
and 1, and it would be more accurate since there are errors. I am going to
try and figure it out on my own now, but if you have an idea it would be
great, thanks again.

"John Bundy" wrote:

sorry i can go to 56 not just 36
Sub main()
For i = 1 To 56
Cells(i, 1).Interior.ColorIndex = i
Next
End Sub

--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"Josh Johansen" wrote:

Thanks so much, I am going to give this a try now, is there a way to
determine what the colorindex is for the 5 colors i want to use for each day?
I will let you know how it goes once I put it in. Thanks!

"John Bundy" wrote:

You need this in the code for each button, see comments and adjust/ask
questions as needed
Private Sub CommandButton1_Click()
ActiveCell.Interior.ColorIndex = 4
Cells(1, 9) = Cells(1, 9) + Cells(ActiveCell.Row, 3)
' the last 3 assumes your number to add is in column 3
'this assumes I1 has your total, change to suit
End Sub
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"Josh Johansen" wrote:

I have been attempting something for some time now and have been
unsuccesfull, maybe it is not possible, but if anyone has any ideas, please
let me know. I would like to be able to select a cell, press a push button
(there would be one for everyday of the week), that cell would then be
highlighted a specific color depending on what day button was pushed (a
different color for each day) at the same time a value from that row that the
cell was selected from would be added to a running total for each day. I
will try and describe it further this way:

abcdef 3543 8
ghijklm 4543 4
nopqrs 4564 6
tuvwxy 2342 5

I would like to select the 4543 cell, press a tuesday button, the cell would
then be highlighted the color i have selected for tuesday, and 4 would be
added to a column outside of the table to maintain a running total. I could
then select 2342 and select tuesday, the cell would be highlighted the same
color as 4543, and the running total would now equal 9. This seems difficult
and may not be possible, any ideas on how to make this work? thank you so
much.

A complex VBA question
 

I was practicing using the functions and what I realize is that I need an
undo button, something where if a user pushed the wrong button or selected
the wrong cell they could undo the action, the undo in the toolbar doesnt
work, is it possible to generate that?

"John Bundy" wrote:

sorry i can go to 56 not just 36
Sub main()
For i = 1 To 56
Cells(i, 1).Interior.ColorIndex = i
Next
End Sub

--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"Josh Johansen" wrote:

Thanks so much, I am going to give this a try now, is there a way to
determine what the colorindex is for the 5 colors i want to use for each day?
I will let you know how it goes once I put it in. Thanks!

"John Bundy" wrote:

You need this in the code for each button, see comments and adjust/ask
questions as needed
Private Sub CommandButton1_Click()
ActiveCell.Interior.ColorIndex = 4
Cells(1, 9) = Cells(1, 9) + Cells(ActiveCell.Row, 3)
' the last 3 assumes your number to add is in column 3
'this assumes I1 has your total, change to suit
End Sub
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"Josh Johansen" wrote:

I have been attempting something for some time now and have been
unsuccesfull, maybe it is not possible, but if anyone has any ideas, please
let me know. I would like to be able to select a cell, press a push button
(there would be one for everyday of the week), that cell would then be
highlighted a specific color depending on what day button was pushed (a
different color for each day) at the same time a value from that row that the
cell was selected from would be added to a running total for each day. I
will try and describe it further this way:

abcdef 3543 8
ghijklm 4543 4
nopqrs 4564 6
tuvwxy 2342 5

I would like to select the 4543 cell, press a tuesday button, the cell would
then be highlighted the color i have selected for tuesday, and 4 would be
added to a column outside of the table to maintain a running total. I could
then select 2342 and select tuesday, the cell would be highlighted the same
color as 4543, and the running total would now equal 9. This seems difficult
and may not be possible, any ideas on how to make this work? thank you so
much.

A complex VBA question
 

Let me know if you have trouble with your first problem, you should get it no
problem! the second can be as simple as
Private Sub CommandButton2_Click()
ActiveCell.Interior.ColorIndex = 2
Cells(1, 9) = Cells(1, 9) - Cells(ActiveCell.Row, 3)

End Sub

put that on another button and they can go back to ANY cell that was
highlighted and change it back white as well as subtract its number from the
total. You might want to throw some errorhandling in there if they might
select a cell that is not highlighted.
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"Josh Johansen" wrote:

I was practicing using the functions and what I realize is that I need an
undo button, something where if a user pushed the wrong button or selected
the wrong cell they could undo the action, the undo in the toolbar doesnt
work, is it possible to generate that?

"John Bundy" wrote:

sorry i can go to 56 not just 36
Sub main()
For i = 1 To 56
Cells(i, 1).Interior.ColorIndex = i
Next
End Sub

--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"Josh Johansen" wrote:

Thanks so much, I am going to give this a try now, is there a way to
determine what the colorindex is for the 5 colors i want to use for each day?
I will let you know how it goes once I put it in. Thanks!

"John Bundy" wrote:

You need this in the code for each button, see comments and adjust/ask
questions as needed
Private Sub CommandButton1_Click()
ActiveCell.Interior.ColorIndex = 4
Cells(1, 9) = Cells(1, 9) + Cells(ActiveCell.Row, 3)
' the last 3 assumes your number to add is in column 3
'this assumes I1 has your total, change to suit
End Sub
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"Josh Johansen" wrote:

I have been attempting something for some time now and have been
unsuccesfull, maybe it is not possible, but if anyone has any ideas, please
let me know. I would like to be able to select a cell, press a push button
(there would be one for everyday of the week), that cell would then be
highlighted a specific color depending on what day button was pushed (a
different color for each day) at the same time a value from that row that the
cell was selected from would be added to a running total for each day. I
will try and describe it further this way:

abcdef 3543 8
ghijklm 4543 4
nopqrs 4564 6
tuvwxy 2342 5

I would like to select the 4543 cell, press a tuesday button, the cell would
then be highlighted the color i have selected for tuesday, and 4 would be
added to a column outside of the table to maintain a running total. I could
then select 2342 and select tuesday, the cell would be highlighted the same
color as 4543, and the running total would now equal 9. This seems difficult
and may not be possible, any ideas on how to make this work? thank you so
much.

A complex VBA question
 

Everything worked out well, thanks a lot John!

"John Bundy" wrote:

Let me know if you have trouble with your first problem, you should get it no
problem! the second can be as simple as
Private Sub CommandButton2_Click()
ActiveCell.Interior.ColorIndex = 2
Cells(1, 9) = Cells(1, 9) - Cells(ActiveCell.Row, 3)

End Sub

put that on another button and they can go back to ANY cell that was
highlighted and change it back white as well as subtract its number from the
total. You might want to throw some errorhandling in there if they might
select a cell that is not highlighted.
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"Josh Johansen" wrote:

I was practicing using the functions and what I realize is that I need an
undo button, something where if a user pushed the wrong button or selected
the wrong cell they could undo the action, the undo in the toolbar doesnt
work, is it possible to generate that?

"John Bundy" wrote:

sorry i can go to 56 not just 36
Sub main()
For i = 1 To 56
Cells(i, 1).Interior.ColorIndex = i
Next
End Sub

--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"Josh Johansen" wrote:

Thanks so much, I am going to give this a try now, is there a way to
determine what the colorindex is for the 5 colors i want to use for each day?
I will let you know how it goes once I put it in. Thanks!

"John Bundy" wrote:

You need this in the code for each button, see comments and adjust/ask
questions as needed
Private Sub CommandButton1_Click()
ActiveCell.Interior.ColorIndex = 4
Cells(1, 9) = Cells(1, 9) + Cells(ActiveCell.Row, 3)
' the last 3 assumes your number to add is in column 3
'this assumes I1 has your total, change to suit
End Sub
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"Josh Johansen" wrote:

I have been attempting something for some time now and have been
unsuccesfull, maybe it is not possible, but if anyone has any ideas, please
let me know. I would like to be able to select a cell, press a push button
(there would be one for everyday of the week), that cell would then be
highlighted a specific color depending on what day button was pushed (a
different color for each day) at the same time a value from that row that the
cell was selected from would be added to a running total for each day. I
will try and describe it further this way:

abcdef 3543 8
ghijklm 4543 4
nopqrs 4564 6
tuvwxy 2342 5

I would like to select the 4543 cell, press a tuesday button, the cell would
then be highlighted the color i have selected for tuesday, and 4 would be
added to a column outside of the table to maintain a running total. I could
then select 2342 and select tuesday, the cell would be highlighted the same
color as 4543, and the running total would now equal 9. This seems difficult
and may not be possible, any ideas on how to make this work? thank you so
much.