Enter a value in a cell depending on the format of another cell

[brooky]

Hello

I would like to enter a 1 or a 0 into a cell, depending on whether or not a different cell has a border around it - any ideas?

Alternatively can use the condition of the colour of another cell (eg red or not) or whether it has bold text in it or not.

Many thanks

 

[Domski]

I think the problem you are going to have will be actually testing if the border/colour/font has been changed as the normal events like worksheet calculate and worksheet change will not pick this up as it isn't the contents of the worksheet itself that would be being updated.

You could use a worksheet activate event to update the values each time the sheet is selected but any changed made would not be reflected until you selected another sheet and then went back to it.

Dom

 

[jim may]

What Domski has said holds true - Any formatting that is done to a cell DOES NOT trigger the Change event nor does it cause a recalculation of your worksheet.

Here's a UDF you can paste into a Standard module and in your ws enter

=TestForRed(A1)


Function TestForRed(rng As Range) As Boolean
Application.Volatile
If rng.Interior.ColorIndex = 3 Then
TestForRed = 1
Else
TestForRed = 0
End If
End Function

 

[brooky]

brilliant - thanks.

 

[brooky]

Jim May - that works in old excel but doesn't seem to work in new excel - do you know how I can make it work in Excel 2007?

Thanks

 

[jim may]

it works for me in xl2007.

You can remove the "As Boolean" from the 1st line of the function -- I ended up not using it, so it is redundant.

Should be just

Function TestForRed(rng As Range)