Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formatting Character length
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formatting Character length
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 -- daddylongleg ----------------------------------------------------------------------- daddylonglegs's Profile: http://www.excelforum.com/member.php...fo&userid=3048 View this thread: http://www.excelforum.com/showthread.php?threadid=52398 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formatting Character length
Look at Format|conditional formatting.
JohnB wrote: 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. -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formatting Character length
Why not prevent it then - look at Data=Validation
-- Regards, Tom Ogilvy "JohnB" wrote in message ... 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formatting Character length
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 JohnB wrote: 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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formatting Character length
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 FormatConditional 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formatting Character length
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. "Ken Johnson" wrote in message ups.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 FormatConditional 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formatting Character length
On Sat, 18 Mar 2006 19:23:20 -0600, "JohnB" wrote:
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. 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formatting Character length
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 "Ron Rosenfeld" wrote in message ... On Sat, 18 Mar 2006 19:23:20 -0600, "JohnB" wrote: 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. 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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formatting Character length
You're welcome John.
Thanks for the feedback. You couldn't possibly complain about the service now, could you? :-) Ken Johnson |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formatting Character length
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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formatting Character length
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 len38 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 |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formatting Character length
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional formatting based on character count | Excel Worksheet Functions | |||
cell text length conditional formatting - Excel 2003 | Excel Discussion (Misc queries) | |||
character length in data forms | Excel Discussion (Misc queries) | |||
assigning character length in a cell | Excel Discussion (Misc queries) | |||
length of character data | Excel Discussion (Misc queries) |