Problem Setting Interior Property Of Cells

[yumbelie]

I can set the property of a cell using the interior object fine, as long as as the procedure is called from a Sub - command button or whatever. The problem is, I want a row to be coloured when a user makes an alteration to a cell - and the Interior object *WONT* color the cell if called from within a function or a sub embedded in the function. What I want to do is this:

When I call a function, it colors the entire row - e.g, in Excel cell I put =MyCellColouringFunc(Boolvalue) - The function is now called, and if Boolvalue evaluates true the entire row is coloured 80's Neon Yellow. The value returned to the cell will be Boolvalue - e.g. Cell contents will be True or False, and the entire row will be yellow.

I don't want the user to have to click a button to update the colors in all the records if that can be avoided.

Current Code:

Function MyCellColouringFunc(Boolvalue As String) As String

If Boolvalue = "True" Then

#work out current row, then

For x = 1 To 13 'I have 13 colums I want to shade

ActiveSheet.Cell(currentrow, x).Interior.ColorIndex = 23
ActiveSheet.Cell(currentrow, x).Interior.Pattern = xlSolid

Next x

End If

MyCellColouringFunc = Boolvalue

End Function


One final note, I've tried using 'Range' instead of Cell, and defining the sheet instead of using ActiveSheet - Now, I'm pretty new to VB, so I may be missing something fundemental (like u can't call scripts from functions or something) - But *ANY* help is appericated, the more detailed the better

Many Thanks

Joseph

 

[RobBroekhuis]

Custom worksheet functions aren't intended to modify formatting properties. However, you can do the same thing using different techniques. Probably, your case calls for a worksheet event handler. Say, the cell that triggers the color change is A1 on sheet "sheet1", and the value that triggers the color change is "yellow".
You can insert an event handler on your worksheet code page by going into the VBE and double-clicking on the "sheet1" object under Microsoft Excel Objects" in the project explorer window. This will open the code page. Now select from the left dropdown above the code window "worksheet", and from the right dropdown "change". This inserts the header structure for your event handler. Modify it to something like:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
if target.address="$A$1" then
if target="yellow" then
currentrow=(find the row you want to format)
range(cells(currentrow,1),cells(currentrow,13)).interior.colorindex=23
end if
end if
End Sub

Depending on the details of your application, you may need an "else" clause to return to the original color if the condition is NOT met.
Rob

 

[yumbelie]

excellent, much appericated - does exactly what I need it to. Many thanks for your assistance

Joseph