Formula For Spreadsheet

  steviegee 14:01 06 Nov 07
Locked

Is there a formula I can apply to an entire spreadsheet so that when I write in a cell I get warned if it goes over a 60 characters like changing colour or something? Thanks.

  xania 14:44 06 Nov 07

To count the number of characters in a cell use the LEN function
i.e LEN(C8) will return the total number of characters in the cell C8 including intermediary spaces.

I suggest you then have another column using the IF function
i.e. =IF(D8>60,"Y"," ") in D8 will leave D8 blank except when the data cell is greater than 60 characters.

SO far as I know there is no way in Excel to change colour conditionally, but try this for size
click here

  VoG II 16:42 06 Nov 07

You should be able to use Conditional Formatting, on the CF dialog select Formula Is and enter

=LEN(A1)>60

and select your format. Then use the Format Painter to apply to the whole sheet.

An alternative is a pop-up message. right click the sheet tab and select View Code. Copy the following and paste it into the code window.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Len(Target.Value) > 60 Then MsgBox prompt:="Cell " & Target.Address(False, False) & " >60 characters", Buttons:=vbOKOnly
End Sub

then close the code window.

  bretsky 17:00 06 Nov 07

Bookmarked.

  xania 17:08 06 Nov 07

Thought VOG might have a better way.

  steviegee 10:47 07 Nov 07

Thankyou

This thread is now locked and can not be replied to.

What is ransomware and how do I protect my PC from WannaCry?

What I learned from my mentor, Oscar-winning VFX supervisor Phil Tippett

Siri vs Google Assistant