Chandoo.org - Learn Excel & Charting Online - Forums Tag: formatting

Chandoo.org - Learn Excel & Charting Online - Forums Tag: formatting - Recent Posts http://chandoo.org/forums/tags/formatting Ask Excel and Charting Questions, Muse about Visualizations, Learn and Share - Discussion Forums - Chandoo.org en-US Fri, 24 May 2013 08:22:44 +0000 http://bbpress.org/?v=1.0.1 <![CDATA[Search]]> q http://chandoo.org/forums/search.php SirJB7 on "How to verify cell alignment left, right, center or other?" http://chandoo.org/forums/topic/how-to-verify-cell-alignment-left-right-center-or-other#post-103224 Sat, 18 May 2013 02:14:19 +0000 SirJB7 103224@http://chandoo.org/forums/ @NARAYANK991 Hi! Literally read and taken, you're right, Fabian Chaverri asked what you wrote, so he'd safely use the CELL function with the "prefix" parameter. Of course assuming the only text constraint. In any case he has the choice, as OP always have. My advice is clear. Regards! NARAYANK991 on "How to verify cell alignment left, right, center or other?" http://chandoo.org/forums/topic/how-to-verify-cell-alignment-left-right-center-or-other#post-103223 Sat, 18 May 2013 02:05:05 +0000 NARAYANK991 103223@http://chandoo.org/forums/ Hi Pablo , You will have to take Microsoft to task for that. If you re-read the first sentence by OP : I need to find a way to identify if a cell was aligned to the right, left or center. I did not find a no-aligned requirement. Narayan SirJB7 on "How to verify cell alignment left, right, center or other?" http://chandoo.org/forums/topic/how-to-verify-cell-alignment-left-right-center-or-other#post-103219 Sat, 18 May 2013 01:53:12 +0000 SirJB7 103219@http://chandoo.org/forums/ @NARAYANK991 Hi! Yes, I did, but if the attribute of the HorizontalAlignment property for that cell is different (0 for non aligned, -4131 or &HEFDD& for left, -4108 or &HEFF4& for centered and -4152 or &HEFC8& for right), it should retrieve a different result, a different character in this case as it doesn't retrieve the actual value. Well, it's a "prefix" not an "alignment" parameter after all. Otherwise it's so badly implemented that it shouldn't be used at all, IMHO. Regards! NARAYANK991 on "How to verify cell alignment left, right, center or other?" http://chandoo.org/forums/topic/how-to-verify-cell-alignment-left-right-center-or-other#post-103217 Sat, 18 May 2013 01:47:26 +0000 NARAYANK991 103217@http://chandoo.org/forums/ Hi Pablo , You are right , but have you considered that for TEXT , the default is left-aligned ? Narayan SirJB7 on "How to verify cell alignment left, right, center or other?" http://chandoo.org/forums/topic/how-to-verify-cell-alignment-left-right-center-or-other#post-103214 Sat, 18 May 2013 01:38:34 +0000 SirJB7 103214@http://chandoo.org/forums/ @NARANYANK991 Hi! Despite of that (i.e., it works only for cell containing text) it retrieves the same character (apostrophe ') for both no aligned and left aligned cells. Regards! NARAYANK991 on "How to verify cell alignment left, right, center or other?" http://chandoo.org/forums/topic/how-to-verify-cell-alignment-left-right-center-or-other#post-103212 Sat, 18 May 2013 01:29:45 +0000 NARAYANK991 103212@http://chandoo.org/forums/ Hi Fabian , To add to whatever has been posted , the CELL function with "prefix" for the first parameter , returns information about the cell alignment , as in : =CELL("prefix",A1) However , there are two problems with this function : 1. This works only for cells which contain text ; it does not work with cells which have numbers in them 2. Any time you change the alignment , you need to either recalculate the cell which has the CELL function in it , by pressing F2 and ENTER , or recalculate the entire worksheet by pressing F9. Narayan SirJB7 on "How to verify cell alignment left, right, center or other?" http://chandoo.org/forums/topic/how-to-verify-cell-alignment-left-right-center-or-other#post-103211 Sat, 18 May 2013 01:20:48 +0000 SirJB7 103211@http://chandoo.org/forums/ Hi, Fabian Chaverri! First of all welcome to Chandoo's website Excel forums. Thank you for your joining us and glad to have you here. As a starting point I'd recommend you to read the green sticky topics at this forums main page. There you'll find general guidelines about how this site and community operates (introducing yourself, posting files, netiquette rules, and so on). Among them you're prompted to perform searches within this site before posting, because maybe your question had been answered yet. Feel free to play with different keywords so as to be led thru a wide variety of articles and posts, and if you don't find anything that solves your problem or guides you towards a solution, you'll always be welcome back here. Tell us what you've done, consider uploading a sample file as recommended, and somebody surely will read your post and help you. And about questions in general... If you haven't performed yet the search herein, try going to the topmost right zone of this page (Custom Search), type the keywords used in Tags field when creating the topic or other proper words and press Search button. You'd retrieve many links from this website, like the following one(s) -if any posted below-, maybe you find useful information and even the solution. If not please advise so as people who read it could get back to you as soon as possible. And about this question in particular... I'm afraid that Excel don't exposes cell or range alignment properties to be used by formulas, so you'll need a UDF (user defined function) that does the job. Here's the code: ----- <pre><code>Option Explicit Function sAlignment(pRng As Range) As String ' constants Const ksAlign = "X, No alignment, Left aligned, Centered, Right aligned" ' declarations Dim sAlign() As String, I As Integer ' start sAlign = Split(ksAlign, ",") ' process Select Case pRng.Cells(1, 1).HorizontalAlignment Case xlGeneral I = 1 Case xlLeft I = 2 Case xlCenter I = 3 Case xlRight I = 4 End Select ' end sAlignment = sAlign(I) End Function</code></pre> ----- Use it as: =sAlignmentType(A1) Just advise if any issue. Regards! Fabian Chaverri on "How to verify cell alignment left, right, center or other?" http://chandoo.org/forums/topic/how-to-verify-cell-alignment-left-right-center-or-other#post-103207 Sat, 18 May 2013 00:29:27 +0000 Fabian Chaverri 103207@http://chandoo.org/forums/ I need to find a way to identify if a cell was aligned to the right, left or center. I was able to validate if the cell contained either a values or text (istext, isnumber) or if it contained certain types of format (cell("format",_) or cell("type",_)). Does anybody know a way? Thanks! SirJB7 on "Take your Excel column and row titles with you." http://chandoo.org/forums/topic/take-your-excel-column-and-row-titles-with-you#post-102961 Fri, 17 May 2013 01:35:07 +0000 SirJB7 102961@http://chandoo.org/forums/ Hi, Gray Mike! First of all welcome to Chandoo's website Excel forums. Thank you for your joining us and glad to have you here. As a starting point I'd recommend you to read the green sticky topics at this forums main page. There you'll find general guidelines about how this site and community operates (introducing yourself, posting files, netiquette rules, and so on). Among them you're prompted to perform searches within this site before posting, because maybe your question had been answered yet. Feel free to play with different keywords so as to be led thru a wide variety of articles and posts, and if you don't find anything that solves your problem or guides you towards a solution, you'll always be welcome back here. Tell us what you've done, consider uploading a sample file as recommended, and somebody surely will read your post and help you. And about questions in general... If you haven't performed yet the search herein, try going to the topmost right zone of this page (Custom Search), type the keywords used in Tags field when creating the topic or other proper words and press Search button. You'd retrieve many links from this website, like the following one(s) -if any posted below-, maybe you find useful information and even the solution. If not please advise so as people who read it could get back to you as soon as possible. And about this question in particular... Select a cell so as to keep fixed columns at left and rows at top (usually B2), then go to View tab, Window group, Freeze icon, and choose the required option. Regards! Gray Mike on "Take your Excel column and row titles with you." http://chandoo.org/forums/topic/take-your-excel-column-and-row-titles-with-you#post-102954 Fri, 17 May 2013 01:20:55 +0000 Gray Mike 102954@http://chandoo.org/forums/ I would like to know the keyboard short cut to make column or row titles/headers be announced as I move around in a large spreadsheet. I once knew how to do this, but now I cannot remember the trick, and can't seem to find anyone who can tell me. I hope you good people can be of some help to me. Thanks in advance for the time and consideration. Gray Mike SirJB7 on "Find and replace text by color" http://chandoo.org/forums/topic/find-and-replace-text-by-color#post-99526 Wed, 01 May 2013 17:35:56 +0000 SirJB7 99526@http://chandoo.org/forums/ Hi, Ben4576! No problem about the rules, it was your first time here and it was only a guide to help you. You're now changing and not slightly the specs you firstly wrote. Let me see if I get you now: - you have cells with text, e.g. "the quick brown fox jumped over the lazy dog" - as per your original request if all the cell was in read then it should be replaced by an hyperlink and added to a list - now you say that if only "brown fox" it's in red, the hyperlink appears, for all the cell or just for the text in red?, and if "lazy dog" is in red too? Regards! Ben4576 on "Find and replace text by color" http://chandoo.org/forums/topic/find-and-replace-text-by-color#post-99498 Wed, 01 May 2013 16:12:55 +0000 Ben4576 99498@http://chandoo.org/forums/ I apologize for breaching any netiquette rules, this is my first stab at posting in a forum. I did perform searches both internal and external of chandoo, but came up fruitless. The coding you provided is, by far, the closest I have gotten to a solution yet. My excel sheet uses red to designate certain text to be hyperlinked within lines of text in the same cell. The formula you suggested only selects red text for hyperlinking when it does not share a cell with text in other colors. Is it possible to do that? My end goal is to have the ability to search an excel sheet that is full of text to select the text colored red and place hyperlink coding on either side, without eliminating any text within the cells. SirJB7 on "Find and replace text by color" http://chandoo.org/forums/topic/find-and-replace-text-by-color#post-99379 Wed, 01 May 2013 04:12:17 +0000 SirJB7 99379@http://chandoo.org/forums/ Hi, Ben4576! First of all welcome to Chandoo's website Excel forums. Thank you for your joining us and glad to have you here. As a starting point I'd recommend you to read the green sticky topics at this forums main page. There you'll find general guidelines about how this site and community operates (introducing yourself, posting files, netiquette rules, and so on). Among them you're prompted to perform searches within this site before posting, because maybe your question had been answered yet. Feel free to play with different keywords so as to be led thru a wide variety of articles and posts, and if you don't find anything that solves your problem or guides you towards a solution, you'll always be welcome back here. Tell us what you've done, consider uploading a sample file as recommended, and somebody surely will read your post and help you. And about your question... If you haven't performed yet the search herein, try going to the topmost right zone of this page (Custom Search), type the keywords used in Tags field when creating the topic or other proper words and press Search button. You'd retrieve many links from this website, like the following one(s) -if any posted below-, maybe you find useful information and even the solution. If not please advise so as people who read it could get back to you as soon as possible. Despite of these, you'd surely want to give a look at this file: <a href="https://dl.dropboxusercontent.com/u/60558749/Find%20and%20replace%20text%20by%20color%20%28for%20Ben4576%20at%20chandoo.org%29.xlsm" rel="nofollow">https://dl.dropboxusercontent.com/u/60558749/Find%20and%20replace%20text%20by%20color%20%28for%20Ben4576%20at%20chandoo.org%29.xlsm</a> It uses a dynamic named range ReferenceTable located in the Reference worksheet, which can be changed within the code. The Update Refs button will search in all worksheets different from Reference cells with font color in red, will add them in the ReferenceTable, and set crossed links from and to the reference and the referrer. This is the code: ----- <pre><code>Option Explicit Sub UpdateRefs() ' constants Const ksWS = "Reference" Const ksReference = "ReferenceTable" Const ksHyperlink1 = "=HYPERLINK(" Const ksLinkPrefix = "#" Const ksAdmiral = "!" Const ksComma = "," Const ksQuote = """" Const ksHyperlink2 = ")" ' declarations Dim ws As Worksheet, wsR As Worksheet, rng As Range Dim lReference As Long, sName As String, sLink As String, sText As String Dim I As Long, J As Long, C As Range ' start Set wsR = Worksheets(ksWS) Set rng = wsR.Range(ksReference) lReference = rng.Rows.Count ' process With rng For I = 1 To ThisWorkbook.Worksheets.Count Set ws = ThisWorkbook.Worksheets(I) sName = ws.Name If sName <> ksReference Then For Each C In ws.UsedRange If C.Font.Color = vbRed Then ' position lReference = lReference + 1 .Cells(lReference, 1).Value = sName .Cells(lReference, 2).Value = C.Address(False, False, xlA1) ' formula or value If C.HasFormula Then .Cells(lReference, 3).Value = C.Formula Else .Cells(lReference, 3).Value = C.Value End If ' links ' in reference sText = ksHyperlink1 & _ ksQuote & ksLinkPrefix & ws.Name & ksAdmiral & _ C.Address(False, False, xlA1) & _ ksQuote & ksComma & ksQuote & _ Format(C.Value) & _ ksQuote & ksHyperlink2 .Cells(lReference, 3).Formula = sText ' in worksheet sText = ksHyperlink1 & _ ksQuote & ksLinkPrefix & wsR.Name & ksAdmiral & _ .Cells(lReference, 3).Address(False, False, xlA1) & _ ksQuote & ksComma & ksQuote & _ Format(C.Value) & _ ksQuote & ksHyperlink2 C.Formula = sText End If DoEvents Next C End If Set ws = Nothing Next I End With ' end Application.CutCopyMode = False Set C = Nothing Set rng = Nothing Set wsR = Nothing Beep End Sub</code></pre> ----- Just advise if any issue. Regards! Ben4576 on "Find and replace text by color" http://chandoo.org/forums/topic/find-and-replace-text-by-color#post-99323 Tue, 30 Apr 2013 21:18:50 +0000 Ben4576 99323@http://chandoo.org/forums/ But I do not want to delete the Red text in the process. Just place the code on either side. Ben4576 on "Find and replace text by color" http://chandoo.org/forums/topic/find-and-replace-text-by-color#post-99322 Tue, 30 Apr 2013 21:13:56 +0000 Ben4576 99322@http://chandoo.org/forums/ I want to build a macro to search my worksheet for Red text and replace the text with a line of code to link the text. Is there any way to find and replace text by color? bobhc on "Find only those cells with "filled" formatting" http://chandoo.org/forums/topic/find-only-those-cells-with-filled-formatting#post-98548 Fri, 26 Apr 2013 15:10:29 +0000 bobhc 98548@http://chandoo.org/forums/ Good day polarisking Your Parentheses were stopping auto open of the website polarisking on "Find only those cells with "filled" formatting" http://chandoo.org/forums/topic/find-only-those-cells-with-filled-formatting#post-98539 Fri, 26 Apr 2013 13:21:50 +0000 polarisking 98539@http://chandoo.org/forums/ Nevermind . . . I was lazy and should have done a little research beforehand. Here's the solution, in case anyone's interested. Select the range you'd like to search. 1. Press Ctl-F to invoke the Find/Replace dialog box and then select the Find tab. 2. With nothing in the Find What box, click on the Format button, which will open the format dialog box. 3. In the Format dialog box, go to the Fill tab and then select the appropriate colour. 4. Click on OK to close the Format dialog and then choose Find All. The address of all the cells with that particular colour will be listed in the find and replace box. 5. Select one and then press Ctl-A to select all other similar coloured cells. 6. Close the dialog. 7. Type whatever you'd like to put in the cell and press Ctl-Enter source: <a href="http://www.techfuels.com/everything-else/5732-finding-cell-colour-excel.html" rel="nofollow">http://www.techfuels.com/everything-else/5732-finding-cell-colour-excel.html</a> polarisking on "Find only those cells with "filled" formatting" http://chandoo.org/forums/topic/find-only-those-cells-with-filled-formatting#post-98538 Fri, 26 Apr 2013 13:13:26 +0000 polarisking 98538@http://chandoo.org/forums/ Using Go To ... Special, you can find and isolate cells with either blanks or non-blanks, then using Ctl-Enter you can replace the contents globally. Is there a way to find and isolate cells (with/without a macro) filled with a particular color? border? etc.? Thank you in advance shrivallabha on "Best Option to Control/Limit/Guide Text Entry into Cell (VBA only option?)" http://chandoo.org/forums/topic/best-option-to-controllimitguide-text-entry-into-cell-vba-only-option#post-86552 Mon, 25 Mar 2013 17:49:35 +0000 shrivallabha 86552@http://chandoo.org/forums/ Target refers to the cell on which you have clicked so it is the same as Activecell. So I normally use the Target word in case worksheet_based events. Instead of asking user to enter his initials you can use something like Application.Username and skip the initials inputbox altogether. What sort of error handling do you want to add? You haven't specified anything in that regard. <pre><code>Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) Dim Comment As String Dim Date_Stamp As String Dim Initials As String Dim vbResult As VbMsgBoxResult If Not Intersect(Target, Range("T10:T309")) Is Nothing Then Cancel = True vbResult = MsgBox("Do you want to insert comment?", vbYesNo, "COMMENT") If vbResult = vbNo Then Exit Sub Comment = InputBox("Please Enter Comment (no initials or dating required)", "Comment") Initials = InputBox("Please Enter Your Initials", "Initials") Date_Stamp = DateValue(Now) Target.Value = "*" & Initials & "-" & Date_Stamp & " " & Comment & "* " & Target.Value End If End Sub</code></pre> olddirtybaird on "Best Option to Control/Limit/Guide Text Entry into Cell (VBA only option?)" http://chandoo.org/forums/topic/best-option-to-controllimitguide-text-entry-into-cell-vba-only-option#post-86539 Mon, 25 Mar 2013 16:17:03 +0000 olddirtybaird 86539@http://chandoo.org/forums/ Hi Shrivallabha, Thanks for the reply! I was thinking the same thing and went with a double-click event. I really like the code you mentioned asking/confirming whether they want to insert a comment - Cheers! Here's what I'm currently using, but I need to add that option you mentioned and also some on error proofing ' Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) Dim Comment As String Dim Date_Stamp As String Dim Initials As String If Not Intersect(Target, Range("T10:T309")) Is Nothing Then Comment = InputBox("Please Enter Comment (no initials or dating required)", "Comment") Initials = InputBox("Please Enter Your Initials", "Initials") Date_Stamp = DateValue(Now) ActiveCell.Value = "*" & Initials & "-" & Date_Stamp & " " & Comment & "* " & ActiveCell.Value ActiveCell.Select End If End Sub' shrivallabha on "Best Option to Control/Limit/Guide Text Entry into Cell (VBA only option?)" http://chandoo.org/forums/topic/best-option-to-controllimitguide-text-entry-into-cell-vba-only-option#post-86076 Sat, 23 Mar 2013 04:42:15 +0000 shrivallabha 86076@http://chandoo.org/forums/ Users may not want to scroll up to click insert button. You can also try a worksheet based event. Right click on Sheet tab and choose "View Code". Paste this code: <pre><code>Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim Comment As String Dim Date_Stamp As String Dim User_Code As String Dim vbResult As VbMsgBoxResult If Target.Column = 10 Then 'It will give prompt on Column J vbResult = MsgBox("Do you want to insert comment?", vbYesNo, "COMMENT") If vbResult = vbNo Then Exit Sub User_Code = InputBox("Please Enter User Code", "User Code") Comment = InputBox("Please Enter Comment", "Comment") Date_Stamp = DateValue(Now) Target.Value = User_Code & " - " & Date_Stamp & " " & Comment End If End Sub</code></pre> olddirtybaird on "Best Option to Control/Limit/Guide Text Entry into Cell (VBA only option?)" http://chandoo.org/forums/topic/best-option-to-controllimitguide-text-entry-into-cell-vba-only-option#post-86032 Fri, 22 Mar 2013 20:17:11 +0000 olddirtybaird 86032@http://chandoo.org/forums/ Oh! Sorry. That is useful and makes complete sense. I misunderstood "user code", which represents the user initials now and that's great. Thanks again! DaveTurton on "Best Option to Control/Limit/Guide Text Entry into Cell (VBA only option?)" http://chandoo.org/forums/topic/best-option-to-controllimitguide-text-entry-into-cell-vba-only-option#post-85973 Fri, 22 Mar 2013 17:00:30 +0000 DaveTurton 85973@http://chandoo.org/forums/ I was including two forcing them to put their user code in and ensuring it was in the right format. But whichever works best for your needs olddirtybaird on "Best Option to Control/Limit/Guide Text Entry into Cell (VBA only option?)" http://chandoo.org/forums/topic/best-option-to-controllimitguide-text-entry-into-cell-vba-only-option#post-85972 Fri, 22 Mar 2013 16:56:54 +0000 olddirtybaird 85972@http://chandoo.org/forums/ Hi Dave, Thanks for the quick reply. I think I'll give that a shot. One question though, was there any reason for the 2 input boxes? I should be good with just one. What I plan on doing now is concatentating their old comments that exist in the cell(s). For example, whatever they entered in last week for the project updates in cell T10. And then having the input box gather their new comment/updates to place at the beginning. So something like this probably: ' Comment = InputBox("Please Enter Your Update", "Update") Date_Stamp = DateValue(Now) ActiveCell.Value = " - " & Date_Stamp & " " & ActiveCell.Value Thanks again! DaveTurton on "Best Option to Control/Limit/Guide Text Entry into Cell (VBA only option?)" http://chandoo.org/forums/topic/best-option-to-controllimitguide-text-entry-into-cell-vba-only-option#post-85969 Fri, 22 Mar 2013 16:28:39 +0000 DaveTurton 85969@http://chandoo.org/forums/ Use the below: <pre><code>Sub Comments() Dim Comment As String Dim Date_Stamp As String Dim User_Code As String User_Code = InputBox("Please Enter User Code", "User Code") Comment = InputBox("Please Enter Comment", "Comment") Date_Stamp = DateValue(Now) ActiveCell.Value = User_Code & " - " & Date_Stamp & " " & Comment End Sub</code></pre> Put a button on the sheet and assign the macro to it. It will put the comment etc in the active cell olddirtybaird on "Best Option to Control/Limit/Guide Text Entry into Cell (VBA only option?)" http://chandoo.org/forums/topic/best-option-to-controllimitguide-text-entry-into-cell-vba-only-option#post-85967 Fri, 22 Mar 2013 16:10:47 +0000 olddirtybaird 85967@http://chandoo.org/forums/ Hi, This is my first post...I love this site. In regards to my question, I have a spreadsheet that has cells setup for user text/comment entry. For example, Cell T10 has text entered by Bob: "Project on track. Next update will be provided 28MAR13-BH22MAR13" The purpose of this cell is to capture commentary and updates from users regarding their tasks or projects, however, due to different users and their level detail (human factor), it has become difficult to quickly understand which comment is the most recent...for example imagine this: "Project on track. Waiting on approved budget. Delayed by 1 month. Project is good so far" So the problem becomes how do I start to encourage via Excel sheet setup or code that users should always place an initial and data stamp prior to their most recent comment and also make it the first item to read in the cell...ideally something like this: "*SRB 22MAR13 - Project on track* Waiting on approved budget. Delayed by 1 month. Project is good so far" I'm going to go ahead and assume that the best and probably the easiest method to do something like this is to create a userform/text box that activates by event when they double-click that cell or perhaps a command button that initiates the code. Based on this logic I'm thinking that with the userform text box I could receive their text updates and then automatically have code afterwards that places an initial and time stamp after complete and puts it at the first line in the cell... Also, a key item to note is that I can not consider fonts or text formatting, hence, my example includes only text characters (*) to highlight updates because I have other workbooks linked to these cells looking at those comments. I.e. Boss/Project Mgmt office opens his global spreadsheet. And within this spreadsheet he can see these text updates from this other workbook. (FYI, I'm not a big fan of having "shared" workbooks"..tons of crashes with multiple users...) Sorry for the long explanation, but I couldn't figure out a more concise way to truly describe my vision of the functionality and how it relates to this issue :) That being said, does anyone have any suggestions or do they think this userform code is the best way to ensure all comments follow such a consistent and clean format for legibility? I greatly appreciate your advice and help! Love this forum! Thank you, Stephen Debraj Roy on "Way to Lock Column Widths?" http://chandoo.org/forums/topic/way-to-lock-column-widths#post-81263 Thu, 28 Feb 2013 20:08:17 +0000 Debraj Roy 81263@http://chandoo.org/forums/ Hi Skidragon, Thanks for sharing the MACRO... :) By the way.. * Select all Cells.. and Change Protection "Unlocked" * Select Only A:R Column .. Change Protection to "Locked" * Protect Worksheet... Password is Optional... * Provide all Permission.. Except Format Column.. (uncheck) Will also "Lock the width of specific columns".. :) but suggesting you to ignore it.. Regards, =DEC2HEX(3563) skidragon02 on "Way to Lock Column Widths?" http://chandoo.org/forums/topic/way-to-lock-column-widths#post-81253 Thu, 28 Feb 2013 18:36:36 +0000 skidragon02 81253@http://chandoo.org/forums/ @sgmpatnaik - Here it is: <pre><code>Sub Column_Widths() Dim ws As Worksheet Application.ScreenUpdating = False For i = 3 To 29 With Sheets(i) .Columns("A:A").ColumnWidth = 5.57 .Columns("B:B").ColumnWidth = 11.71 .Columns("C:C").ColumnWidth = 8.86 .Columns("D:D").ColumnWidth = 8.86 .Columns("E:E").ColumnWidth = 9.43 .Columns("F:F").ColumnWidth = 2.86 .Columns("G:G").ColumnWidth = 17# .Columns("H:H").ColumnWidth = 7.29 .Columns("I:I").ColumnWidth = 32# .Columns("J:J").ColumnWidth = 32# .Columns("K:K").ColumnWidth = 16.29 .Columns("L:L").ColumnWidth = 7.71 .Columns("M:M").ColumnWidth = 10.29 .Columns("N:N").ColumnWidth = 4.86 .Columns("O:O").ColumnWidth = 7.14 .Columns("P:P").ColumnWidth = 4.86 .Columns("Q:Q").ColumnWidth = 6.29 .Columns("R:R").ColumnWidth = 5.57 .Rows("3:103").EntireRow.AutoFit End With Next Application.ScreenUpdating = True End Sub</code></pre> Sammy on "Format Cells based on specific text" http://chandoo.org/forums/topic/format-cells-based-on-specific-text#post-80895 Wed, 27 Feb 2013 08:19:08 +0000 Sammy 80895@http://chandoo.org/forums/ Yes, the list box is connected to a cell and gives a numerical value, and the cells in col A contain a vlookup formula Well, I used the below formula on top of my actual formula and it works fine when I change the locations, even though I ended up with a messy and LENGTHY formula :( Parts of the trade for not wanting to use a macro! =if(a12=1, text(formula, "0.00%"), if(a12=2, text(formula, "0.00"), text(formula,"0"))) Thanks a lot for your time and help though. Much appreciated! Thanks, Sam Luke M on "Format Cells based on specific text" http://chandoo.org/forums/topic/format-cells-based-on-specific-text#post-80747 Tue, 26 Feb 2013 20:54:03 +0000 Luke M 80747@http://chandoo.org/forums/ So, the list box is connected to a cell and gives a numerical value, and the cells in col 1 contain some sort of lookup formula, I presume? Rather than just trying on a different computer, trying on a new workbook would tesst if the theory is correct and/or there's something else goofy going on in workbook. Trying to debug the problem, can we start small and just try to get 1 cell to change formatting based on another. Then, try having multiple cells change. Then, copy/extend the rule to other cells in other columns. Trying ot figure out at what point does the problem emerge. =/