Conditional Formatting Character length

  • Thread starter Thread starter JohnB
  • Start date Start date
J

JohnB

All,

Here is what I want to do. If a cell's character length is more than 38
characters (if len()>38), then the cell gets highlighted red. If not, then
nothing. I would like to have this were it happens automatically instead of
having to run a macro each time. Is there a macro that can do this? What I
am trying to do is make sure that when a user types in a description in a
cell, that the character length is 38 or less.

Thanks.
 
You can just use "formula is" in Conditional formatting and use formula

=LEN(A1)>38

but you might want to also look at Data Validation to prevent input o
+38 characters (although it doesn't prevent pasting of data
 
Look at Format|conditional formatting.
All,

Here is what I want to do. If a cell's character length is more than 38
characters (if len()>38), then the cell gets highlighted red. If not, then
nothing. I would like to have this were it happens automatically instead of
having to run a macro each time. Is there a macro that can do this? What I
am trying to do is make sure that when a user types in a description in a
cell, that the character length is 38 or less.

Thanks.
Click to expand...
 
Hi John

you do not need a macro or code...

if you are on a form, you can use conditional formatting

from the menu -->
Format, Conditional Formatting...

Condition1 -->
Expression is
Len([controlname]) > 38

choose fill to be red

Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access

remote programming and training
strive4peace2006 at yahoo.com
 
Hi John,
use Conditional Formatting which you should find in the Format menu.
Use this eg as a guide...
Say the range of cell to be formatted so that more than 38 characters
results in a red fill happens to be A1:D10, then...

1. Select A1:D10 then go Format>Conditional Formatting
2. Under the blue heading "Condition 1" is a box with "Cell Value Is".
Click on that box's down arrow then select ""Formula Is".
3. Click in the next box to the right and type the following formula...

=LEN(A1)>38

4. Click on the Format... button to see the Format Cells dialog
5. Click on the Patterns tab of that dialog then select red then OK
6. Click OK on the conditional Formatting dialog.

Hope this makes sense.

Ken Johnson
 
All Thanks,

I would use data validation, but the problem is that sometimes users import
data into the cells and then have to make sure that the description is 38 or
less characters. Thanks Again for all your help. That was a lot easier
than I thought.

Thanks
John B.
 
All,

Here is what I want to do. If a cell's character length is more than 38
characters (if len()>38), then the cell gets highlighted red. If not, then
nothing. I would like to have this were it happens automatically instead of
having to run a macro each time. Is there a macro that can do this? What I
am trying to do is make sure that when a user types in a description in a
cell, that the character length is 38 or less.

Thanks.
Click to expand...

Format/Condtional Format/Formula Is: =LEN(A1)>38

Format to taste.

Why not also use data validation and set the text length to equal or less than
38?


--ron
 
I have one more question. If a user copys something from another sheet and
copies it to the conditional format range, the conditional formatting does
not work unless the user selects to "match destination formatting." Any
ideas on how to have the conditional formatting pick up when the source
formatting is kept? Not all my users are "excel techno."

Thanks
John B
 
You're welcome John.
Thanks for the feedback.
You couldn't possibly complain about the service now, could you?
:-)
Ken Johnson
 
That's because a straight copy copies the formatting also. Use paste
special value, or formula. Of course that's your problem, you user
won't use that insted of copy/paste. Sorry. ed
 
Hi John
I thought that protecting the sheet would prevent the user from pasting
a new conditional format over yours. I first unlocked all the cells,
otherwise no pasting could occur, then protected the sheet, however, I
did not achieve the sort of protection you are after, which is a pity.
I have come up with a rather clumsy solution using a WorksheetChange
event procedure.
Everytime the sheet changes, the range of cells that change are loaded
into a two dimensional array called TargetArray. Then Cell A1 is copied
and pasted into the range of changed cells (Called Target by excel).
This resets the conditional formatting that may have been overwritten
if the change was the result of the user pasting instead of typing.
Then the values stored in TargetArray are fed into the range of changed
cells.
One important assumption is that A1 is conditionally formatted with the
=LEN(A1)>38 formula and that A1 is not a cell that the user can paste
into to destroy its conditional formatting. This will be satisfied if
A1 is locked and the worksheet is protected.
If you don't want A1 to be set up this way then, in the code, change
any reference to A1 to an appropriate cell address.
The code turned out to be a lot more complicated than I had hoped.
My original idea was to have the code do the following...
When the sheet changes, copy A1 then paste special paste formatting
into the changed cells. Unfortunately, even though the code says
exactly that (Target.PasteSpecial Paste:=xlPasteFormats), that is not
all that happens, for some reason A1's value is also pasted.
This meant I had to store the values pasted in by the user into an
array, reset the conditional formatting by pasting A1, then put the
pasted values back into the cells from the array.

If you want to try out this solution then..

1. Copy the code below
2. Right click the worksheet's Sheet Tab then select "View Code" from
the contextual popup.
3. paste the code in place into the Sheet's Code Module, which is the
white space under the boxes with the headings "(General)" on the left
and "(Declarations)" on the right
4. Press Alt + F11 to retun to the worksheet

To test it out, on another sheet, apply conditional formatting to some
cells using a different formula or different cell colour. Copy some or
all of those cells then paste them into the original worksheet.
Hopefully your conditional formatting for len>38 giving red will still
be in place. (It worked for me)

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
On Error GoTo ERRORTRAP
Dim TargetArray As Variant
Dim I As Long, J As Long
Dim TargetRows As Long
Dim TargetColumns As Long
TargetRows = Target.Rows.Count
TargetColumns = Target.Columns.Count
ReDim TargetArray(TargetRows, TargetColumns)
For I = 1 To TargetRows
For J = 1 To TargetColumns
TargetArray(I, J) = Target.Cells(I, J).Value
Next
Next
Range("A1").Copy
Target.PasteSpecial Paste:=xlPasteFormats
For I = 1 To TargetRows
For J = 1 To TargetColumns
Target.Cells(I, J) = TargetArray(I, J)
Next
Next
Application.EnableEvents = True
Application.CutCopyMode = False
Exit Sub
ERRORTRAP: Application.EnableEvents = True
Application.CutCopyMode = False
End Sub

Ken Johnson
 
Hi John,
I must have been doing something silly when I put that code together.
Target.PasteSpecial Paste:= xlPasteFormats does only paste the format
of A1 without pasting the value.
This means that the code need only be...

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
On Error GoTo ERRORHANDLER
Range("A1").Copy
Target.PasteSpecial Paste:=xlPasteFormats
Application.CutCopyMode = False
Exit Sub

ERRORHANDLER:
With Application
.EnableEvents = True
.CutCopyMode = False
End With
End Sub

Ken Johnson
 
Back
Top