Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

VBA Function issue (Entire Row formatting) 1

Status
Not open for further replies.

PureSlate

MIS
Oct 25, 2010
34
US
Hello,

I'm having issues getting a function to work properly. Essentially, I am trying to make a function that will conditionally format a row for the user. (Yes, there is a reason I'm not using conditional formatting.) My problem is that, while I can get similar code working in a subprocedure, I am having issues getting the code to work in a function. Here is a quick example of what I mean:

Code:
Sub YellowNOW2()
    'Turns entire Row Yellow
    ActiveCell.EntireRow.Interior.Color = 65535
End Sub

Code:
Function YellowNOW(Range2 As Range)
    'Does nothing
    Range2.EntireRow.Interior.Color = 65535
End Function

Similarly, when I use this line in my actual function, it does nothing. I believe I do not properly understand how, or if, you can format a row with a function.

Any help would be greatly appreciated.
 
Where and how are called the Sub and the Function ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
The sub I was running manually for my own test purposes, the function I was calling in excel itself with =YellowNOW(A25)

Replacing Range2.EntireRow.Interior.Color = 65535 with YellowNOW = "Yellow" returns the expected "Yellow", but leaving the first line in causes a #VALUE! error.
 
An UDF called from a cell can't change anything in the workbookbut return a calculated value.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
I was afraid it was something like that.

Thanks for the help PHV!
 
hi,

Why not use the Conditional Formatting feature in Excel (no VBA needed!)???

Skip,
[sub]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 
Two fold:

First, I needed the vba for formatting in a longer macro (hence the sub).
Second, I wanted it to be manually added via a function (excel add-in.) It would be easier for the end user to use from the function.
 
First, I needed the vba for formatting in a longer macro
"Needed"? WHY? What is the reason for shading a row? No code reasons, but business reasons.

Skip,
[sub]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 
Well a business reason might be, "the user highlights any selected row green because abc" or "the user highlights certain rows green because xyz and other rows yellow because qrt"

Skip,
[sub]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top