Is there a format or a macro that I can use to change a nine digit zip code into a five digit zipcode. Below is what I have in each cell.
Carolina Clinical Research, Inc.
Suite 606-B
2990 Bethesda Place
Winston-Salem, NC 27103-3315
Is there a way to look at a cell's own value and then format it accordingly using VB?
I have a cell whose value changes based on DDE link to live price data.
Cell is "Q5".
When the new value is greater than the old value - i want to format the pattern color of the cell.
When the new value is less than the old value - i want to format the pattern color of the cell.
Does anyone know how to do this please or is there a way to do this using spreadsheet formulas as oppose to VB?
I need to adjust the attached code as follows;
The data in cell E10 is a date derived from the formula
=IF($C$2<>"",IF(E10="",NOW(),E10),"")
It is a time stamp and works fine. My issue is when I copy and paste the data I am getting the date and time. I just need the date copied not the time. I currently just go in to the date and delete the time from the entry but need to refine the process. Cell E10 is the only issue in the current setup.
Using conditional formatting, I set the font color to Green for Cell G12
=IF( SUM(G12) > C28,SUM(G12),""). This worked. However I needed 6 conditions for this formatting. Excel only allows for 3 conditions. My VBA code is not working. Can someone tell me the correct code?
For cells "G12,O12,G23,O23" I want the font colors to change if their value is
1. greater than or equal to Cell C28 (change to green font)
2. greater than or equal to Cell C29 (change to orange font)
3. greater than or equal to Cell C30 (change to brown font)
4. greater than or equal to Cell D28 (change to green font)
5. greater than or equal to Cell D29 (change to orange font)
6. greater than or equal to Cell D30 (change to brown font)
It is set so that if a value is enter in C28, D28 will have a zero value. Can only use one or the other. Likewise if C29 has a value, D29 will be zero and cannot be used.
The code I used was:.............
I'm trying to analyse all of my bank statenments to see where all my money went!! I've got online banking so I thought it would be a relatively easy process of copying and pasting each month into excel.
Well, that in itself was easy, the problem is with the formatting of the financial numbers. Excel doesn't seem to recognise them as numbers, so at the moment I can't do any manipulation with the numbers. I have tried everything such as:
Format Cells > Number > Number and Currency
Copy and Paste Special > Values only
Copying into Notepad and back into Excel
Even if it appears to have changed the numbers to 'number' or 'currency' formatting by right justifying the numbers, if I try to add up the numbers using the SUM function, it doesn't want to know.
For some reason nothing seems to work - what am I missing?
For an example, I have attached a snippet of the spreadsheet with my starbucks transactions.
Please refer to attached file.
I have data in cell B2:K11
Each cell consist of 12 digits with 1 and 0.
I need to see if digit 1 can have one color font (yellow) and 0 can have other color font (light Orange).
(Either VB Code or Formula would work for me).
I have this code, which is primarily built by the record macro utility. I know that record macro does some unnecessary steps such as selecting while inserting columns etc which is generally never done in vba.
View 5 Replies View RelatedI have a workbook that requires a VB code to help me out with Conditional Formatting. I use Office 2003 which is restricted to 3 conditions, I know there is an add in I can use that would help me do this but other people may use this that wont have the add in. I have decided to use VB if possible to get this done.
I have a range of cells from B22 – T22 in these cells I will be putting codes, when these codes are put into the cells I would like the cells to shade a different colour depending on what code I use. Here is an example of what I mean.
P = blue
S = red
HL = green
ML = magenta
FL = orange
I may have a couple of more codes I will add at a latter time. Is it possible for VB code to do this?
I am working on some data cleanup for some video. I have a lot of time code data, ie. mm:ss (minutes; seconds), and I need to convert it to hh:mm:ss:ff (hhi is hours; ff is frames). It's A LOT of data, and I don't want to do it by hand.
Examples:
2:02
0:55
2:18
2:55
1:41
1:20
1:44
I have two quesxtion:
1. Excel wants to interpret my data as time data and will convert my data if I click on a cell, ie. 2:02 is converted to 2:02:00 AM. That might not be so bad, but sometimes it changes a time that is 00:55 to 12:55:00. So, my first question is...how do I make excel read the numbers exactly as is w/o any conversion? I've tried to mark the cells as Text but that doesn't work, and I can't seem to make a Custom format work. Any ideas?
2. Ideally, I'd like to quickly convert these timecodes to hh:mm:ss:ff. Would macro work on this? So, I'd be taking 2:02 and making it 00:02:02:00. I think I need to solve #1 before I tackle #2, but i could be wrong.
I have the code below in a macro, but cannot figure out how to add the following format to the left and right header:
size 12 Ariel font, Bold and Italic
I cannot seem to get it right!
With ActiveSheet.PageSetup
.LeftMargin = Application.InchesToPoints(0.166)
.RightMargin = Application.InchesToPoints(0.166)
.TopMargin = Application.InchesToPoints(1)
.BottomMargin = Application.InchesToPoints(0.8)
.HeaderMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0.3)
[code].....
I dont know the correct terminology with whick to phrase my question, but I would like to know if its possible to copy say columns A-J on "worksheet A", and copy columns A-J on "worksheet B", each of which have different column widths, and paste them both to "worksheet C"?
My problem is columns A-J on "worksheet A" are perfect and copy and past fine to "worksheet C". But when I copy columns A-J from "worksheet B",which have different widths, and paste them to "worksheet C" right below what I had previously pasted from "worksheet A", the column widths interfere with each other.
I have tried a special paste, and it seems to re format everything above the current page its pasting?
I recorded some code and cleaned it up to apply a conditional format
Sub condi_format_I()
Dim wbBook As Workbook
Dim ws As Worksheet
Dim rng As Range
Set wbBook = ThisWorkbook
Set ws = wbBook.Worksheets("Summary")
Set rng = ws.Range("C4:P52")
With rng
.FormatConditions.Delete
.FormatConditions.Add xlExpression, Formula1:="=C4<0"
.FormatConditions(1).Interior.ColorIndex = 45
End With
End Sub
I would like to apply a custom color index to all cells in range C4:P52 if the value is less than zero.
But when I execute the macro, many cells receive the custom format that do notr match the custom format. Other cells that should receive the custom format do not
I am attempting to modify the code below to, not only reference the ".Item(rCell.Value)", but also include it's formatting. The format for that referenced cell will not necessarily be a "certain" format..so I don't want to have to write out what specific formatting needs to happen.
VB:
If (.exists(Trim(rCell.Value)) And rCell.Offset(, 1).Value = "") Then rCell.Offset(, 1) = .Item(rCell.Value)
The entire code is as follows:
VB:
Sub FillVariablesOnlyBlanks()
Application.ScreenUpdating = False
Dim a, i As Long, rCell As Range, ws As Worksheet[code]....
In B16:T24 I have cells that need shading depending on what code I put in them. I have 5 codes P, BH, S, ML, HD
I would like the cells to change colour, when I put P in them I want them to change to blue, when I put BH in them I want them to change to green, S I want to change to red and so on.
I am adding shapes with text (msoShapeOval) to an image on a worksheet but I cant get the size of the "ShapeRange" to work dependant on the value assigned to "sTxtlen".
I had msgbox's within each Case but none were triggered allthough using another msgbox to show the length of sTxtLen is correct prior to the Select Case.
I have found this code while searching. I would like to know how to call the vba code only when clicking the button. On much bigger projects Excel has to re-calculate all cells everytime i change a cell value.
View 3 Replies View RelatedI am having a little trouble with this code, which runs in my simple but efective ressource overview. It loops through a rather large range and assigns interior colour to the cells based on certain criteria. On my stationary machine (Excel 2003) it takes approximately 15-30 seconds to run the code which is acceptable. On my laptop (Excel 2007) it takes 5+ minutes which is unacceptable. Is there a workaround so as to optimize the speed? Further, when I run this workbook on Excel 2007, even entering an integer in the sheet takes 3-4 seconds, and no code is running! In my first version I used a Change_Event to colour cells on the fly but this was slow and prevented multible cell editing as well as pasting values into the appropriate range.
Public Sub Farvelade()
Dim icolor, Navn As Integer
Dim TargetRow, LastRow, Previous As Long
Dim Target As Range
MsgBox "Det kan tage 15-30 sekunder at opdatere ressourcekalenderen"
Range("A5").Activate
Range("A5"). CurrentRegion.Select
'Set range
ActiveCell.Offset(Selection.Rows.Count, 0).Activate
LastRow = Cells(Cells.Rows.Count, "A").End(xlUp).Row.....................
I changed the code below to add an extra column(E) this worked. However it does not keep the formatting from the top row in column E, How can i change the code to column E to keep the top row formatting ??
Also i added a list to cell A2, but this moves down, I would like to have the selected data move down but the list stays in cell A2, Was this possible ?
I'm building a Excel 2003 workbook that needs more than 3 conditional formats. I've tried the code given in the FAQ: Get Around Excels 3 Criteria Limit in Conditional Formatting but this only works on cells being modified directly. I've also tried the code in the post: VBA Conditional Formatting - Refresh when data changes but neither does the job I need.
I'm using a Format Control to present the user with a list of choices. When the control changes the target cell I want to apply a conditional format to that cell. This image shows the form. When the Format Control selection (in B2) is changed I want to apply conditional formating to the target cell (A2).
There will be up to 10 choices and therefore conditions.
I am working with a large report that needs to be broken out and sent as separate files to recipients for confidentiality purposes. I'd rather not use views/protection since there are many different ways particular people need to see the data, plus it is a very large file and flattening it works to everyone's advantage. My goal is a macro that will copy each tab into separate workbooks, paste special values, and save as each as Cell A1 (or the tab name-same thing). I have tried recording macros and editing (I'm very new to VBA) many times but it's a mess.
View 2 Replies View Related In the attached example, you will find column C which has a bunch of qualitative results. Also, in the file or worksheets, you will find Table A which has a color code range.
I would like to have a conditional format down column C that will reference Table A, regardless if it's on in the same worksheet or on another worksheet within the same file?
Using Excel 2013
I have a macro that, at varying places, changes the formatting of adjacent cells. The formatting code is easy to generate via the macro recorder, but it's long - longer than the macro itself - and I'm using it a couple of times (due to if statements). That makes it difficult to read. It's usable, but looks ugly. I'd like to streamline its appearance.
Is there a way to do something like:
Code:
If range("F5").Value > 0 then
Activecell.Offset(1,0) format = blah
ElseIf Range("F5").Value = 0 then
Activecell.Offset(-1,0) format = blah
End If
The formatting I want is dark gray interior with a border box around it. The macro recorder generated this:
Code:
Sub test2()
'
' test2 Macro
'
'
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
[Code] ............
I am trying to add something to a spreadsheet. I want to do some Conditional Formatting but I am not certain on what the formula would be to accomplish it. I am putting the Cond Format in cell E5 and I need a formula to say: IF F5 AND G5 are BLANK then do the format I set up which is simply to fill the cell with RED. (.ColorIndex = 3)
The next portion of my problem is I need to revise my VBA Coding to check and make sure that there is not any cells in Column E that have the Formatting Active. This is the portion of code I need to alter. I have changed the wording of the message but am not sure on how to change the IF statement:
'Checks to see make sure there are no Actions or Resolutions WITHOUT a Topic
If Sheets("Meeting Minutes").Range("C1").Value = "" Then 'NEEDS TO CHANGE
MsgBox "Oops! You forgot to enter a Topic for Discussion in a cell(s). You MUST have a Topic in order for your Meeting Minutes to transfer to the Master sheet." & vbCr & vbCr & "Please click OK to return to the Minutes sheet so that you may fill in the Topic(s).", vbOKOnly, "CAUTION! PLEASE Note!"
Range("D5").Select
Application.ScreenUpdating = True
Application.StatusBar = ""
Exit Sub
End If
the code i have got is attached below but the problem i am having is when i enter the letter "H" which is calculated in AJ and i enter it fast it comes up with the error "Out Of Stack Space error 28". AJ in the code below is where the cumlative value is stored, and AI is the value that it is measured against it AJ is greater than AI the message is displayed.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rRow As Range
Dim icolor As Integer
Dim ifont As Integer
With Application
.CellDragAndDrop = False
.CutCopyMode = False
End With
If Intersect(Target, Me.Range("TABLE1")) Is Nothing Then Exit Sub
Me.Calculate
For Each rRow In Target.Rows
If Me.Cells(rRow.Row, "AJ") > Me.Cells(rRow.Row, "AI") Then
rRow.ClearContents
MsgBox "No Holidays Left or No Holidays setup Against Them " & Me.Cells(rRow.Row, "AI") & " days."............................
I am trying to determine how to get the code below to fire whenever cell J10 is populated and do nothing when cell J10 is not populated but I can't quite get it. (Cell J10 is manually changed and is not changed based off of a formula)
View 4 Replies View RelatedI have a macro that, when run, needs to read the contents of cell B5, and run the code that it contains.
Cell B5, for example, would contain the text:
Range("B13").Formula = "SUM(D12:D14)"
I need a macro to "execute this code", as if it were in the macro itself.
I have assigned the above to a variable, but am not sure how to execute it.
EG.
Dim the_calc
the_calc = Range("B5").value
Now, how do I run the_calc ?
I have one column that contains a monetary amount (column AQ) , and another that contains text reading either "inflows" or "outflows" (column AC)
When AC says "inflows", AQ should be positive, and when AC says "Outflows", AQ should be negative.
I need the text in column AQ (the monetary amount) to become red when the the opposite is true.
i.e. When AQ is negative and AC says "Inflows", AQ should become red. And when AQ is positive and AC says "Outflows", AQ should become red.
I have two columns. In column B is the date of "last check". I column A is the date of "next check". I would like to have cell A2 in yellow color 334 days after the date entered in cell A3 and than in red color 365 days after the date entered in cell A3. Same thing for cell B2 related to date entered in cell B3. Yellow color in cells announces that check will expire within 30 days and red color that check has been expired.
View 1 Replies View RelatedThis is a conditional formatting problem I have not been able to resolve:
I have a range, say A1:N30
Each cell contains a text/number combination.
I would like to highlight each cell on a specific row if the vale exists within the row above - the issue is that the cell values are not in the same columns.
I have attached a sample workbook with the desired output.
Conditional formatting.xlsx
I am trying to alert our purchasing mgr when order dates are approaching or not meeting our project deadline.
As of now i have the following rulesif order date is due today or past due - redif order date it greater than project date - redif order date is due within 2wks - yellow
Now all I need is a rule where there is an order without a due date but the project deadline is within 2wks (yellow) and past due or due today (red)
KO_04.01.14.xlsx