Formula For Spreadsheet

  steviegee 14:01 06 Nov 07

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


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


  xania 17:08 06 Nov 07

Thought VOG might have a better way.

  steviegee 10:47 07 Nov 07


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