I 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?
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.
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
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 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.
I 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 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.
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?
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
A little background on what i am doing. I have a spreadsheet that tracks when i have blown the dust out of our computers. I have set up conditional formatting so that the text turns red after 300 days and the cell turns yellow after 600 days. However, some computers are in high dust environments. I would like the spreadsheet to tell me to blow the dust out of these sooner. see the attached sheet.
Is it possible to change the format of cell AI3 based on the format of cell C3 and D3? I have C3 and D3 set to turn red based on what is in cell C2 and D2. I would like the following done:
If AI3=C3 & C3 is red, format AI3 blue If AI3=D3 & D3 is red, format AI3 blue Otherwise, leave AI3 unformatted.
Is there "code" for different formatting in a spreadsheet so you can use an IF statement to do something like:
if(A1=blue background,"Yes","No")
or
if(A1=red text,"Yes","No")
etc.?? But replace "blue background" and "red text" with some sort of number code? I want to compile a list of the items that are formatted with certain background colors and/or text colors and then organize only those items into a chart.
Any VBA that I can put into a macro that will convert conditional formatting into fixed formatting..? So when the cell contents/formulas are deleted the formatting remains. Assume that the range I want to convert is A1:D200...
I simply want to find and replace a character within many cells that have the format general using VBA code. The cells are all dates and are seperated by a '.' but i want them to be seperated by a '/'. I have used this code below to do so, it changes the '.' to a '/' fine but it swaps the month with the day e.g. '08.09.2006' would become '09/08/2006'.
I need to run a loop through a column of values (attachment col B) and when it finds a "J" it will apply conditional formatting to a row of 4 cells directly adjacent. The attachment is a theoretical before & after.
Have a cmd button and text box. I need to enter a word in the text box, hit the button, and have excel show me where that text is at on the worksheets (numerous sheets). I have tried unsuccessfully suggestions, but all want to replace the text with something. I do not need to replace the text, just find it.
Months ago I ever posted the following code on Mr. Excel forum but did not get satisfied solutions. Right now slightly changed the code and post here.
I have the following code running on Excel 2000 for many years. Now I just switched to Excel 2003 version and found the code does not work well: It refused to do replace function. Does Excel 2003 version need some additional consideration?
Sub SSRe() Dim CommVolArray As Variant Dim CommAssArray As Variant Dim MyWorkbook As Workbook
I am writing a vba code in which the user can change their old password with the new password. For Eg- User A has password B. He wants to change his password to C, so everytime he logs in with new password, he is able to enter into the file. However, i have no idea what exactly i could do(i mean the codes in vba).
I am trying to create code that I can use to to update a macro module automatically so I don;t have to keep having my users manually import when we have update to the module.
Here is the code I have so far.
Private Sub Workbook_Open() Call UpdateTJMacros 'This is in the module we are replacing Call MyOpenWorkbookMacro
I am looking for a piece of code and can locate the range of a specified string, and replace it with something else. For example, I need to find string "AA" and replace that with "BB", can this be achieved with VBA code?
I am trying to write a code for VBA code for find and replace, I want to find a particular phrase (i.e. 1. Value Added Processing) which is all in one cell and replace it a range of cells of other cells which is contained on a different sheet.
So basically the original 1 cell would be replaced for anywhere between 1 to 20cells. Depending upon what I type in.
Hey Have a spreadsheet with about 150 worksheets in.
In Column B of each worksheet is a list of packages
e.g.
Value Added Processing Business Viability Environment Climate Change
I need to add into that other cells to further divide those packages so it will end up looking like
Value Added Processing GA001 GA003 Business Viability GA005 GA032 GA065 Environment GA023 Climate Change GA030 GA029
I have a separate worksheet which has the 37 different package types along ROW A from Column A to Column AH, then going down underneath each of the heading is the various code numbers as shown above.
Has anyone got a VBA code which will allow excel to search the worksheets, find the package name and then replace it with the package name but also insert the code numbers below it.
