VBA Function issue (Entire Row formatting) 1

[PureSlate]

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:

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

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.

 

[PHV]

Where and how are called the Sub and the Function ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[PureSlate]

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.

 

[PHV]

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

 

[PureSlate]

I was afraid it was something like that.

Thanks for the help PHV!

 

[SkipVought]

hi,

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

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[PureSlate]

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.

 

[SkipVought]

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]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[PureSlate]

Haha, business reasons, yes.

 

[SkipVought]

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]
Just traded in my old subtlety...
for a NUANCE![/sub]