Rowby Goren
asked on
Adding 40 to a number if the cell color is "blue"
Hello
Using Excel 2013 for Windows 2008.
I would appreciate a formula that would add 40 to numbers in a column if the COLOR of the column is a certain color. For example "Blue".
I assume I would need to add a column to receive the results of that formula.
So let's assume the original number is in b2 and the results should be put in C2
Thanks!
Rowby
Using Excel 2013 for Windows 2008.
I would appreciate a formula that would add 40 to numbers in a column if the COLOR of the column is a certain color. For example "Blue".
I assume I would need to add a column to receive the results of that formula.
So let's assume the original number is in b2 and the results should be put in C2
Thanks!
Rowby
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi Kyle Abrahams
I just saw your suggestion. I'll try that first.
Rowby
I just saw your suggestion. I'll try that first.
Rowby
ASKER
Hi Kyle
Is inserting a module the same as inserting a macro?
Not sure how to insert a module in Excel 2013. but will dig around. But if you get this please let me know how to do that.
Thanks
Rowby
Is inserting a module the same as inserting a macro?
Not sure how to insert a module in Excel 2013. but will dig around. But if you get this please let me know how to do that.
Thanks
Rowby
Easiest way is to hit alt + F11 in your workbook
On the project (VBAProject) right click -> insert -> MODULE
(NOT CLASS module)
viola.
On the project (VBAProject) right click -> insert -> MODULE
(NOT CLASS module)
viola.
ASKER
Hi Kyle
Got the module f11 ok.
But not sure how to do the rest with the formula, adding 40 to the column.
The "blue" column in the attached sample is Column B
You will see 3 rows are blue and 2 are red. I only want the addition of 40 to be added to the blue rows in column B.
Can you help with the attached as your guide.
Thanks
Rowby
for-experts-exchange.xlsm
Got the module f11 ok.
But not sure how to do the rest with the formula, adding 40 to the column.
The "blue" column in the attached sample is Column B
You will see 3 rows are blue and 2 are red. I only want the addition of 40 to be added to the blue rows in column B.
Can you help with the attached as your guide.
Thanks
Rowby
for-experts-exchange.xlsm
Attached.
I updated the function so that it can take a range:
Again note that changing the color by default doesn't tell excel to update the formulas. You need to update the value of A or reapply the forumla in column E.
for-experts-exchange.xlsm
I updated the function so that it can take a range:
Public Function CellColor(rng As Range) As Long
CellColor = Cells(rng.row, rng.Column).Interior.ColorIndex
End Function
Again note that changing the color by default doesn't tell excel to update the formulas. You need to update the value of A or reapply the forumla in column E.
for-experts-exchange.xlsm
ASKER
Hi
Looks like it's working perfectly. Give me a day to test it on some files and I'll get you the points. I may have a question on the best way to keep that macro/module as part of my Excel so I can use it on other files. It will ALWAYS be the same blue.
(And some points to ihasham too for that useful link.)
Rowby
Looks like it's working perfectly. Give me a day to test it on some files and I'll get you the points. I may have a question on the best way to keep that macro/module as part of my Excel so I can use it on other files. It will ALWAYS be the same blue.
(And some points to ihasham too for that useful link.)
Rowby
Is this always for yourself? Or are you wanting to publish it to others?
if you only need it for yourself you can use:
http://office.microsoft.com/en-us/excel-help/copy-your-macros-to-a-personal-macro-workbook-HA102174076.aspx
Essentially create a macro which creates a personal work book. (Don't worry, you can delete it after).
Then put the code in THAT module 1 (the personal.xlsb).
That should be available whenever you're on your computer.
if you only need it for yourself you can use:
http://office.microsoft.com/en-us/excel-help/copy-your-macros-to-a-personal-macro-workbook-HA102174076.aspx
Essentially create a macro which creates a personal work book. (Don't worry, you can delete it after).
Then put the code in THAT module 1 (the personal.xlsb).
That should be available whenever you're on your computer.
ASKER
Hi Kyle
Yes, it will always be for myself --- and I'll use that link to do that.
Looking good so far. I'll be awarding points later today or tomorrow. :)
Rowby
Yes, it will always be for myself --- and I'll use that link to do that.
Looking good so far. I'll be awarding points later today or tomorrow. :)
Rowby
Take your time, thanks for letting me know you're testing.
ASKER
Hi
I "always" will be using the attached cell color. But I am having a problem getting the cell color formula to "work".
Can you double check the attached spreadsheet?
Thanks
Rowby
experts-exchange-cell-color.xlsm
I "always" will be using the attached cell color. But I am having a problem getting the cell color formula to "work".
Can you double check the attached spreadsheet?
Thanks
Rowby
experts-exchange-cell-color.xlsm
I'm getting 23 for your cell color.
Did you use the updated function?
Public Function CellColor(rng As Range) As Long
CellColor = Cells(rng.row, rng.Column).Interior.Color Index
End Function
Did you use the updated function?
Public Function CellColor(rng As Range) As Long
CellColor = Cells(rng.row, rng.Column).Interior.Color
End Function
ASKER
Hi
I will double check. A little later today :) And will make sure the updated function is in place.
Thanks
I will double check. A little later today :) And will make sure the updated function is in place.
Thanks
ASKER
Hi Kyle,
I am sure this is so simple, but take a look at attached. I've got things messed up.
All I am updating is the Replacement cost based on Year built.
So if the Year Built cell is Blue (23) (in column L) then the "Replacement cost Per Sq Footage" (based on Column K) cell will have 40 added to it -- results of the formula put in the Column M (Updated replacement cost).
It's exactly as you solution solved it, but in transposing it to my actual page, I've missed something.
I would appreciate your adjusting the attached excel.
Thanks
Rowby
I am sure this is so simple, but take a look at attached. I've got things messed up.
All I am updating is the Replacement cost based on Year built.
So if the Year Built cell is Blue (23) (in column L) then the "Replacement cost Per Sq Footage" (based on Column K) cell will have 40 added to it -- results of the formula put in the Column M (Updated replacement cost).
It's exactly as you solution solved it, but in transposing it to my actual page, I've missed something.
I would appreciate your adjusting the attached excel.
Thanks
Rowby
no excel attached :-P
ASKER
Ooops!
experts-exchange-22.xlsm
experts-exchange-22.xlsm
You had everything exactly right. I'm wondering if the personal workbook wasn't applied for some reason. I brought in the function for this workbook's module and it worked fine.
experts-exchange-22.xlsm
experts-exchange-22.xlsm
ASKER
Your "fixed" version works perfectly.
Thanks!
Thanks!
ASKER
Thanks Kyle for the code and to ihasham for that helpful link.
Rowby
Rowby
ASKER
Thanks for that link. As it happens I have Ktools. Let me see if I can follow the instructions for Ktools to do it....
Rowby