Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Adding 40 to a number if the cell color is "blue"

Posted on 2014-03-03
21
Medium Priority
?
309 Views
Last Modified: 2014-03-05
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
0
Comment
Question by:Rowby Goren
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 12
  • 8
21 Comments
 
LVL 12

Assisted Solution

by:Imtiaz Hasham
Imtiaz Hasham earned 300 total points
ID: 39901287
0
 
LVL 40

Accepted Solution

by:
Kyle Abrahams earned 1700 total points
ID: 39901330
Insert a module with this function code:

Public Function CellColor(row As Integer, col As Integer) As Long
    CellColor = Cells(row, col).Interior.ColorIndex
End Function

Open in new window


(screen shot with the module)
Module
To determine the value of your color:
=CellColor(1,1)

and then the final if statment (assuming 55 was your color):
=IF(CellColor(1,1) = 55, A1+40, A1)

you can then apply it for all columns.  Note that if you change the background color you'll need to go to the reference cell and update it.
0
 
LVL 9

Author Comment

by:Rowby Goren
ID: 39901332
Ah,

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
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 9

Author Comment

by:Rowby Goren
ID: 39901336
Hi  Kyle Abrahams

I just saw your suggestion.  I'll try that first.

Rowby
0
 
LVL 9

Author Comment

by:Rowby Goren
ID: 39901344
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
0
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 39901352
Easiest way is to hit alt + F11 in your workbook

On the project (VBAProject) right click -> insert -> MODULE
(NOT CLASS module)

viola.
0
 
LVL 9

Author Comment

by:Rowby Goren
ID: 39901393
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
0
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 39901637
Attached.

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

Open in new window


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
0
 
LVL 9

Author Comment

by:Rowby Goren
ID: 39901857
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
0
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 39901907
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.
0
 
LVL 9

Author Comment

by:Rowby Goren
ID: 39901931
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
0
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 39903454
Take your time, thanks for letting me know you're testing.
0
 
LVL 9

Author Comment

by:Rowby Goren
ID: 39903720
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
0
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 39904161
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.ColorIndex
End Function
0
 
LVL 9

Author Comment

by:Rowby Goren
ID: 39904211
Hi

I will double check.  A little later today :)  And will make sure the updated function is in place.

Thanks
0
 
LVL 9

Author Comment

by:Rowby Goren
ID: 39904372
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
0
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 39904522
no excel attached :-P
0
 
LVL 9

Author Comment

by:Rowby Goren
ID: 39904683
0
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 39906662
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
0
 
LVL 9

Author Comment

by:Rowby Goren
ID: 39907017
Your "fixed" version works perfectly.

Thanks!
0
 
LVL 9

Author Closing Comment

by:Rowby Goren
ID: 39907023
Thanks Kyle for the code and to ihasham for that helpful link.

Rowby
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

715 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question