Conditionally Format a cell when typing longer text than allowed column width.

[Retko]

Hello,

I want my users to fill excel where I have fixed column width and I dont want then to type longer text. So I want to color text in red for example if they exceed the column width.
How can I do this?
Thank you.

 

[James006]

Hello,

Depending on the font you have selected ... and on the font size you have picked ... the number of characters you can have will be different ...

So, it would be much easier for you to determine your total length by using the LEN function ...

HTH

 

[Retko]

Hello,

Depending on the font you have selected ... and on the font size you have picked ... the number of characters you can have will be different ...

So, it would be much easier for you to determine your total length by using the LEN function ...

HTH

Hello,

well, I don't want to restrict it on number of characters, because 'mmmm' and 'iiii' is quite different in size. I guess I might need some harder solution, if it is possible.

Thanks.

 

[wideboydixon]

Welcome to the forum. I think you'd have to use VBA to achieve your requirements. For example, add a new UserForm (mine was called "MeasureForm") with a single text label (mine was called "MeasureLabel") then attach the following code to the sheet:

Private Sub Worksheet_Change(ByVal Target As Range)


If Target.Count > 1 Then Exit Sub


Dim savedWidth
Dim myForm As New MeasureForm


With myForm.MeasureLabel
    .AutoSize = False
    .Width = 1024
    .Height = 128
    .Font.Name = Target.Font.Name
    .Font.Size = Target.Font.Size
    .Caption = Target.Value
    .AutoSize = True
    savedWidth = .Width
End With


Unload myForm


If savedWidth > Columns(Target.Column).Width Then Target.Font.Color = vbRed


End Sub

This worked for me by changing the text to red when I entered a value that wouldn't fit in the size of the column.

WBD

 

[Retko]

Hello,
thank you for you solution, but this is not exactly what I want. I dont want to use it on the form (the excel application), but just in the normal excel. Is this possible?

Thank you.

 

[wideboydixon]

You have to use a form to measure the text; you can't do it with conditional formatting or any other means - you would have to use VBA.

WBD

 

[Retko]

Hi, is it possible to post your excel with the example solution?

Thank you.