Formula For Spreadsheet

  steviegee 14:01 06 Nov 2007

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 2007

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 2007

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


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 2007


  xania 17:08 06 Nov 2007

Thought VOG might have a better way.

  steviegee 10:47 07 Nov 2007


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

Elsewhere on IDG sites

Galaxy Note 8 vs iPhone X

Awful clip art from 1994 is being tweeted every hour by a bot

iPhone X vs Samsung Galaxy Note 8

Les meilleurs navigateurs internet 2017