R,C address in another worksheet

[teachman]

Hello,

I am trying to expand my knowledge and capabilities in Excel and I now have 'an opportunity to excel' (common US Army Officer's response to a lower rank asking a question). Anyway, I know how to create a cell address using Row,Column when the addresses are all in the same worksheet, but I'm stuck when I need to address a cell in another worksheet.

For example, worksheet(1) is called Front, worksheet(2) is called FrontDES. In a cell in FrontDES I am calling a function I found on the net, thank you very much, that uses Row,Column addressing. So, in I am trying to get the cell contents of the cell Front!AN112. I've tried Front!112,Front!40, Front!(112,40), and some others but nothing works.

Since I enjoy exercising my 60 year old brain, I am willing and eager to learn something new today!

Thanks,

George Teachman

 

[RoryA]

It would probably help if you showed us the function, or at the very least how you would call it if you were referring to a cell on the same sheet.

 

[teachman]

Okay, I can feel some brain pain coming on.

This is the function I harvested from Excel Formula Based on Cell Background Colour |

Function CellColour(Irow As Integer, Icol As Integer) As Long
CellColour = Cells(Irow, Icol).Interior.ColorIndex
End Function

I put this function in the vba space of the worksheet named Front. Cell Front!AN112 (which is 112,40 in r,c) has an interior color of yellow (ColorIndex = 6).

In the worksheet named FrontDES, in cell D10 I try to enter: =IF(ColourCell(Front!112,Front!40)=6, Front!AN40, "") upon leaving this cell I get an error saying the formula is incorrect with the 112 highlighted.

In a further test of the function, in Front!AS112 I enter =IF(ColourCell(112,40)=6,"Yellow","Not Yellow") upon leaving this cell the dreaded #NAME? is displayed. The error is "The formula contains unrecognized text.".

I hope this is enough information to help y'all help me.

Thanks in advance.

George Teachman

 

[RoryA]

The function is CellColour, not ColourCell. It's also badly written.

Try this:

Function CellColour(rg as Range) As Long
Application.Volatile True
CellColour = rg.Interior.ColorIndex
End Function

which you call with:
=CellColour(Front!AN40)
for example. Note that changing a cell colour doesn't trigger a recalculation of your workbook so this function will not be updated until something does cause the workbook to recalculate. Simple rule: colours aren't data, don't try and use them as such.

 

[teachman]

Thanks for the help on the function. However, when I call it, as you so kindly showed me, I still get the #NAME? displayed in the cell. With the error message "This formula contains unrecognized text.".

I copied the function code and pasted it into the Front worksheet vba editor space as the first item.

Any idea as to what I'm doing wrong now?

Thanks,

George

 

[Jonmo1]

Put the code in a new module (not the module named after the sheet)

Click Insert - Module
Paste the code there.

 

[RoryA]

Yes - it must go into a normal module, not a worksheet module.

 

[teachman]

Thanks for the answer. I never would have thought about putting it into a normal module. I just assumed that it should go into the worksheet where it was to be used. I have never used a function. That gives away the limits of my capabilities. However, they say an old dog can learn new tricks. This one just learned a half trick, maybe.

Rory, I want to thank you for your time, patience, and good work.

 

[RoryA]

You are most welcome.