Multiple active cells and color

[steff657]

I have been asked by my father to create him a sheet for work, he isn't very good with computers and I haven't used excel or vba for years.

I have managed to automate some tasks for him but there is one I can't workout, I am trying to create a macro to alter the values by 1 when a combination is pressed.

The idea is to allow him to quickly add or subtract multiple values for highlighted cells, for example, he can ctrl + LC on cells b2, b6 and b9 and subtract them by 1.

The code i have at the moment does what I need but only for the active cell and what I would like is the macro to run on all highlighted cells and ideally but not essential, to automatically change to green or red depending on whether or not a value was added or subtracted.

Current code

Sub addOne()

ActiveCell = ActiveCell.Value + 1

End Sub

Any help would be greatly appreciated.

 

[Joe4]

Welcome to the Board!

Try this:

Sub AddOne()

    Dim cell As Range
    
    For Each cell In Selection
        cell.Value = cell.Value + 1
        cell.Interior.Color = vbGreen
    Next cell

End Sub

The subtraction would be very similar (the changes should be pretty obvious).

 

[steff657]

Welcome to the Board!

Try this:

Sub AddOne()

    Dim cell As Range
   
    For Each cell In Selection
        cell.Value = cell.Value + 1
        cell.Interior.Color = vbGreen
    Next cell

End Sub

The subtraction would be very similar (the changes should be pretty obvious).

This is exactly what I needed thank you very much

 

[Joe4]

You are welcome!

And welcome back to the fun world of Excel and VBA!

 

[steff657]

You are welcome!

And welcome back to the fun world of Excel and VBA!

Thanks you again for the help

I have been getting back into it, it's amazing how much you can forget

Would it be alright for me to pick your brain again on something?

I have the spreadsheet set to change a couple of dates automatically and i have been trying to get something else automated but can't seem to get it to work, I am trying to set a column up to automatically reduce values by 1 based on the day, for example, today is the 2nd and the value is 55 tomorrow I want it to decrease to 54 automatically, an additional feature I am also trying to do is set the postivie values to turn green but when they become negative values to turn red.

Is this possible or am I aiming way to high?

Again thank you very much for your help

 

[Joe4]

I am trying to set a column up to automatically reduce values by 1 based on the day, for example, today is the 2nd and the value is 55 tomorrow I want it to decrease to 54 automatically

If they decrease by a value of one, every day, one way to go about it is to store the initial value and initial date in a cell somewhere.
Let's say we have the initial date of 8/15/2020 in cell A1 and initial value of 55 in cell B1.
Then to get the value for the current date, you can just use:

=B1+A1-TODAY()

an additional feature I am also trying to do is set the postivie values to turn green but when they become negative values to turn red.

This can be done super easy using Conditional Formatting.
See: Conditional Formatting in Excel