?
Solved

Assigning cell fill color via function

Posted on 2011-10-20
11
Medium Priority
?
392 Views
Last Modified: 2012-05-12
Private Function FormulaFiller(idata As String, iStart As String, iend As String)
    
    Dim rgCopy As Range, rgDelete As Range, rgFill As Range, rgClear As Range
    
    Set rgCopy = Evaluate(iStart & ":" & iend) 
    Set rgFill = Evaluate(idata)        
    
    Set rgFill = Range(rgFill, rgFill.Worksheet.Cells(Rows.count, rgFill.Column).End(xlUp)) 
    Set rgFill = rgCopy.Resize(rgFill.Rows.count)          

    Set rgDelete = rgCopy.Offset(1, 0).Resize(rgCopy.Worksheet.UsedRange.Rows.count)
    rgDelete.ClearContents
    
    rgCopy.AutoFill rgFill, xlFillCopy
    Application.CutCopyMode = False

End Function

Open in new window


Hi, I have the above formula filling the formulas down according to what I send in.

It get called from

Call FormulaFiller("D14 ", "E14", "H14")

What I want to do is, assign background color to (light grey) to all the cells that have formulas including the start of range (E14...H14) all the way down to end of rows.

I want to know how to do the coding (if possible since I dont have a loop)
Eg:
rgFill.Interior.Color = RGB(237, 237, 4)

Any help is appreciated!
0
Comment
Question by:Shanan212
11 Comments
 
LVL 24

Accepted Solution

by:
StephenJR earned 2000 total points
ID: 37003458
Don't think I understand - maybe this?

rgFill.SpecialCells(xlCellTypeFormulas).Interior.Color = RGB(237, 237, 4)

Open in new window


But you can't change a cell's formatting using a formula.
0
 
LVL 42

Expert Comment

by:dlmille
ID: 37004027
not without conditional formatting, anyway :)
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 37004998
Is there something wrong with:
rgFill.Interior.Color = RGB(237, 237, 4)

Open in new window

as you posted originally?
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 24

Expert Comment

by:StephenJR
ID: 37005367
I must say I'm surprised the OP's code works at all. I thought a formula could only put a result in the cell, but one lives and learns.
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 37005422
As far as I can see it's a function not being called from a worksheet judging by this:

>>It get called from
Call FormulaFiller("D14 ", "E14", "H14")


0
 
LVL 24

Expert Comment

by:StephenJR
ID: 37005439
I see, so if you call a function that way you can do much more, thanks Rory.
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 37005488
When not called from a worksheet cell, a function can do anything a sub can do. The only difference is that one returns a value.
0
 
LVL 24

Expert Comment

by:StephenJR
ID: 37005499
Is there any advantage to doing that? How does it differ from a sub with arguments?
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 37005528
It's more logical. (in this particular question, there is no real advantage to it, since nothing is being returned from the function)
If you are performing a calculation and want to use a return value, it makes sense to use a function. You could do it with a sub and pass an argument ByRef but there's no point to that (unless perhaps you want multiple return values and don't want to use an array/UDT/object as the return type.)
Some people actually recommend that everything should be a function - subs would simply return some sort of success indicator (useful when you call a routine from a larger routine, and want to exit the calling routine if the called routine fails).
0
 
LVL 24

Expert Comment

by:StephenJR
ID: 37005576
Thanks again Rory, I will check this out and not sidetrack this thread any more, although I've noticed the answerers here usually have more interest in the questions than the askers.
0
 
LVL 13

Author Closing Comment

by:Shanan212
ID: 37006106
This worked like a charm :)

As for the code I used, it did not work (color) somehow :/
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

839 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