Colouring Lines Of Text In Comments With VBA
Aug 2, 2008
I currently have some code which reads a stock file, adding up the stock for each individual item (which may be in one or more locations in the warehouse) and then placing the total stock count in a cell with the detail (of how much stock is in each location, the location and the expiry date) and a separate line for each location, in a comment.
The comment is constructed one line at a time as the code finds the stock records, see code below:
With shtWorking.Range(strStartColumn & intRow + 2)
oldComment = .Comment.Text
.Comment.Delete
.AddComment oldComment & vbNewLine & strComment
.Comment.Shape.TextFrame.AutoSize = True
End With
All this works very nicely, but I want to colour individual lines depending on the expiry date. This can be done manually in Excel, but I cannot find a way of colouring individual lines of text in a comment using VBA.
So, does anyone know of a way I can colour individual lines of text in a comment, using VBA?
View 9 Replies
ADVERTISEMENT
Apr 20, 2009
Here is a picture example of what I mean, just randomly found on google:
http://www.projectwoman.com/uploaded...ent-763577.jpg
Maybe an insignificant thing I want, but can the line from the comment connecting to the red indicator be removed? Added an example since my comments show up below the selected cell with VBA code.
View 8 Replies
View Related
Jun 5, 2014
I have a VBA that can add a comment based on the cell value, however, I need it to do it for the whole worksheet and not just one cell. I am pretty new to using VBA's, so this is the best code I could find that would perform what I need. If there is a way to have it do run a vlookup, instead of me listing all the cases, that would be great as well.
VB:
Sub AddComment()
Dim strText As String
With Range("s6")
Select Case UCase(.Value)
Case "UPT": strText = "UnPaid Tardy/Late"
[Code] .....
View 5 Replies
View Related
Nov 8, 2012
I have a situation where textual comments are entered into a cell where the comments are supposed to begin with a date in mm/dd format. Some enter all 5 characters but some enter m / d in if appropriate to the date, e.g., 9/6 vs 09/06.
I'm trying to take the left 5 characters and compare that to the current date - 7 to ID aged comments and highlight that cell. I currently do this by populating a variable LastUpdate (dim as string) with the first 5 characters, then run this IF statement.
If LastUpdate < Date - 7 Then
Range("O" & i).Select
Selection = "Comments over 7 days old."
End If
This works find except in situations where there is a zero for the first character after the "/", such as "11/05." The code doesn't properly calculate the date and assumes it's old. "11/5" works just fine. I thought I could try translating the variable into a proper date. I "borrowed" this formula from another post herein: =DATE(2012, MID(N12,1,FIND("/",N12)-1), MID(N12,FIND("/",N12)+1,2)). This works fine as a formula to translate the characters into a date but I don't know how to do this same thing in vba.
View 5 Replies
View Related
Dec 14, 2011
I have created a worksheet to be used by a number of people at work and my issue is how to add sufficient instructions for some of the less tech minded users.
I have entered text in the comment box to 'assist', but would like to add either a picture WITH the text or something to the effect of "for further asistance and make the last part a hyperlink.
View 1 Replies
View Related
Dec 28, 2008
I noticed that I have several (@200) comments that when visible displays Cell X Commented by (my work user number) in the lower left corner of the excel window. I'm not sure how to change this so its the company name instead of my work number. I'd be using a loop to go through each comment but need help with the line that would change this part.
View 8 Replies
View Related
May 2, 2008
I have an automated formula which i have to fill down to line 250, yet in the majority of cases i only need it to say line 20, and as a result i have loads of lines stating "Not Found" has anyone got a way to hide any lines containing either "Not Found" or a blank cell??
View 9 Replies
View Related
Dec 17, 2008
I have a series of cells, each having multiple lines of text within separated by hard return breaks. For example, cell A1 has the following text:
John Smith
123 Main St.
New York, NY
I want to extract line 1 into cell B1, line 2 into cell C1, and line 3 into cell D1.
Is this possible without VBA or Macros? I'm using Excel 2008 for Mac OSX so that's not an option.
View 9 Replies
View Related
Sep 24, 2013
I need to do in excel, clean same lines and text to columns.
In the 1fs sheet "Sheet1" is the file I need to clean same lines and text to columns;
In the 2sd sheet "final" is the result I need in the end of the clean lines and text to columns;
View 6 Replies
View Related
Sep 7, 2009
I'm writing a macro in excel that edits a text file runs another program that uses this text file as its input and then stores the results. Perameters are then changed in the input file and it is then repeted over and over again. One of the parts I am stuck on is I need to delete the last 5 lines in the input file called INPfile.txt. See below:............
This section is always at the end of the file, is 5 lines long and always starts with [DEMANDS] and always ends with [END].
View 2 Replies
View Related
Jul 21, 2006
Is there a way to make the CONCATENATE formula put all the text on different lines? It keeps combining some on the same lines and I want them all on separate - like a list.
View 13 Replies
View Related
Oct 23, 2008
In column A I have product names, in B I have product categories and in C:H the product categories from column B are titles and the name of the subcategory is written in the cell. Because there are mutlipule sub categories that a product can belong to the report produces mirrored lines with one sub category on each line. What I need to do is combine all of these subcategory entries into one cell separated by a comer (,).
View 2 Replies
View Related
Jul 12, 2013
Is it possible to limit a text box to 6 lines (whether forced or automatically wrapped).
View 2 Replies
View Related
May 23, 2006
I have checked the forum about reading from text files, but I wasn't able to find and similar thread. Here is the situation: I have one text file, but first 204 characters are the file header. 204. character is an asterisk. Then, the records begin. Following is the beginning of the file:
$$GNCSBU/PML/0002 TCPX 11006 IST Bayplan 23.05.06 14:35:57 TRIST 11006 *
And it continues:.....................
View 2 Replies
View Related
Oct 27, 2006
For the following formula:
=”TEXT1”&”TEXT2”
The result is:
TEXT1TEXT2
Is there any kind of modifier that I can use to force the output to multiple lines, i.e.:
TEXT1
TEXT2
If this wasn’t a formula I could just use ALT+ENTER. Wrapping the text will not work because I need fixed break points not dependent on the text box width.
View 5 Replies
View Related
Jan 10, 2007
I have a cell containing a address. I have used alt-enter to separate lines of the address. I have a userform that contains a textbox which which loads the cell containing the address. The problem is once the useform is loaded the address in the textbox is all on the same line and contains a funny looking back the front P character where I have used alt-enter in the cell to make a new line. This ones got me knackered.
View 2 Replies
View Related
Aug 7, 2006
Is it possible to get the following to work.
If cell A2 has the number 5 in it (as a result of a calculation in that cell), is it possible to get the next five cells in that cell to colour themselves in?
Obviously, as and when the value in cell A2 changes, i need the number of cells coloured in to change as well.
In addition to this, the rows that this will happen in will vary week to week so i'm not sure if VBA is the way to do this.
Also would there be any problem with negative figures as when that happens i don't want any cells to colour in at all.
View 9 Replies
View Related
Oct 10, 2008
I have a spreadsheet that contains data I import from various sources. Once the import is complete I need to colour various cells based on their contents. Normally I'd use conditional formatting, but there are more than 3 conditions so I need to use VBA (preferrably that i can turn into a macro and assign to a button to click once I've finished the imports each time)
eg: Everytime a cell content is the word "RED" , colour it red - then the same with various other colours.
Google has found me a change event piece of code but it doesnt work when you're cutting and pasting some info.
View 9 Replies
View Related
Jul 24, 2014
I am trying to cut and paste from a PDF document into excel and a grab of text when pasted sits in three boxes instead of one. How can I manage this?
View 9 Replies
View Related
Apr 22, 2007
I need to extract lines in a huge text file (more than 300,000 records ) based on one condition. for e.g.
02/03/07 123456789 hsjksk sjdlsl
05/03/07 323453789 hsjksk sjdlsl
04/03/07 123456789 hsjksk sjdlsl
02/03/07 123456789 hsjksk sjdlsl
I need extract of lines where the date and the digits are the same. in above example it should extract only record line 1 and record line 4. Some body advised me to try MSAccess , but I have never used MS Access and have no clue about it , hence i don't want to use it. Is there a way in VBA to code this ?
View 9 Replies
View Related
Aug 3, 2007
I have 5 columns. The first 4 columns only contain 1 line each, the last column can contain anywhere from 1-6 lines. How can I do a sort and keep all the text in the last column together? By way, it was imputed on separate lines.
View 9 Replies
View Related
Aug 7, 2008
I have to extact a HUGE (100MEG) .TAB file into Excel 2003. Using the 'import text' fundction it only allows me to import the frst 66536 lines and tells me that I can import the rest by repeating the process, somehow excluding what I have already imported.
View 9 Replies
View Related
Jun 26, 2007
i try to input an txt into a excel spreadsheet, how do skip to certain lines or number? for example if the txt is like
1,2,3,4,5,6,7,8
and i want the excel sheet to start at the the third number
3
4
5
6
7
8
View 8 Replies
View Related
Sep 12, 2007
How can I break this onto 3 lines in Visual Basic Editior to make it more readable:
MsgBox "Only enter data in white cells." & vbNewLine & "Yellow cells contain formulas or dates entered automatically", &
vbNewLine & "Do not insert blank rows. Copy row(s) and Insert/Paste" , vbInformation, "Spreadsheet by GJF"
View 4 Replies
View Related
Apr 23, 2008
I have a several large delimited text files around 800,000+ lines. It cannot all be pasted into excel due to the 65,000 line limit. For my purposes, I only need about 1 in every 1,000 lines from the text file anyways. Is there a way to only bring in the lines I want? Here is the test file layout:.................
I only need the lines that start:
"01","001","------"
I tried running some macros that I found through searching the forums, but I couldn't get any to work.
View 3 Replies
View Related
Dec 4, 2012
I have a rather large database that I'm trying to automate colours in, based on data that is inserted into Column 'D'. I've used the following code (with some success), but it colours columns that I don't want to colour:
VB:
Sub KeyCellsChanged()
Dim Cell As Object
For Each Cell In Range("D1:D5000")
[Code].....
That works fine - but the problem I have is that I only want to colour columns A:N, Q, T, V, AB:AE, etc. (random columns and not always together). I have already tried to replace parts (as follows), but get all sorts of errors (in particular Run-time error 1004):
VB:
If Cell = "Rabbit" Then
Cell.Range("A:N,Q, T, V, AB:AE").Interior.ColorIndex = 42
Is there someway that a line of code can determin which cells to colour in the row, or alternatively code that will colour the columns a specific colour and make them stay that colour when the first code above is used?
View 3 Replies
View Related
Jan 27, 2009
Just trying to work out the best way to be able to determine autoshape colours using cell values. For example I have 12 shapes that need to coloured either Red, Green or Orange depending on a specific cells value, 1 for Red, 2 for Green and 3 for Orange.
I have sort of managed to successfully complete this for 1 of the shapes, by using the RGB Fill option (Using 3 = Red, 4 = Green and 45 = Orange). But cannot for the life of me work out how to do this for multiple shapes using different cell triggers. Below is the code that I currently have and the spreadsheet that is linked to:
View 5 Replies
View Related
Oct 18, 2011
How do i have colouring to a reference cell what i mean is that on certain formula i get BUY or Sell on that cell. As per the conditional formatting i have made the colour change BLUE FOR "BUY" & RED FOR "SELL"
On second sheet i link the above TEXT., but the COLOUR OF THE TEXT Does not come.
Does CONDITIONAL Formatting come with reference cell.
View 7 Replies
View Related
Dec 22, 2006
how I might be able to colour certain cells in a row a specific colour when a specific value is input.
For instance:
The value "A" is put into row A1. A1 and C1 cell colours change to green
The value "B" is put into row A2. A2 and C2 cell colours change to orange
etc. (not sure how many colours yet)
I sort of have a script set up, but there are certain things I dont know how to do. Like target the specific cells that need colouring.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 0 Then Exit Sub
Select Case Target.Value
Case "A"
Target.Interior.ColorIndex = 1
Case "B"
Target.Interior.ColorIndex = 2
Case "C"
Target.Interior.ColorIndex = 3
Case Else
Target.Interior.ColorIndex = 0
End Select
End Sub
View 9 Replies
View Related
Aug 2, 2006
I have a gone through quite a few topics in this forum.....but couldnt find answer to my question....may be because its too basic. I have a worksheet with 10 columns. I am trying fill colour in the rows that have "text" in column J (in other words.....their column J is not blank). I figured out how to fill the colour in the cells in column J when they are empty, but cant figure out how to colour the entire row from A:J.
View 4 Replies
View Related
