Multiple active cells and color

steff657

New Member
Joined
Aug 31, 2020
Messages
3
Office Version
  1. 2019
Platform
  1. Windows
  2. Mobile
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.
 

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,950
Office Version
  1. 365
Platform
  1. Windows
Welcome to the Board!

Try this:
Code:
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

New Member
Joined
Aug 31, 2020
Messages
3
Office Version
  1. 2019
Platform
  1. Windows
  2. Mobile
Welcome to the Board!

Try this:
Code:
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,950
Office Version
  1. 365
Platform
  1. Windows
You are welcome!

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

steff657

New Member
Joined
Aug 31, 2020
Messages
3
Office Version
  1. 2019
Platform
  1. Windows
  2. Mobile
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 :eek:

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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,950
Office Version
  1. 365
Platform
  1. Windows
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:
Excel Formula:
=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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,970
Messages
5,599,106
Members
414,287
Latest member
Rbwester

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top