VB Code To Replace Conditional Formatting
Mar 20, 2009
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?
View 4 Replies
ADVERTISEMENT
Jun 30, 2006
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:.............
View 2 Replies
View Related
Apr 29, 2014
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).
View 5 Replies
View Related
Jul 18, 2007
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
View 3 Replies
View Related
Mar 19, 2009
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.
View 4 Replies
View Related
Jul 3, 2008
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?
View 9 Replies
View Related
Dec 11, 2006
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 Related
Jan 23, 2008
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.....................
View 8 Replies
View Related
Jun 12, 2008
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
Nov 20, 2013
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
View 14 Replies
View Related
Nov 5, 2008
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
View 9 Replies
View Related
Sep 16, 2013
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.
dust 1.xlsx
View 2 Replies
View Related
Mar 20, 2013
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.
Possible???
View 3 Replies
View Related
May 5, 2009
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.
View 5 Replies
View Related
Jul 5, 2013
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...
View 9 Replies
View Related
Feb 24, 2003
I have a cell that contains text with multiple formatting.
When I use find and replace, the formatting of this text all reverts back to the same style.
How can I overcome this?
(I am using Excel 97)
View 5 Replies
View Related
Sep 11, 2006
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'.
Cells.replace What:=".", Replacement:="/", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
I have noticed once the code is run it changes the format of the cells to date, even tho i include the 'ReplaceFormat:=False' function.
View 6 Replies
View Related
Feb 9, 2009
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.
View 2 Replies
View Related
Jul 2, 2009
How can I reduce the code to perform this task ?
View 3 Replies
View Related
Jul 1, 2012
How to modify this code so that it actually adds to the list of results rather than replace:
Code:
Sub EncID_IP()
'.:: Extract EncID
'
Dim lRow As Long ' East to West
Dim bCount As Byte '1,2,3
[Code] .......
View 4 Replies
View Related
Apr 12, 2007
I want to search column K for the word "test" and for each match, replace it with what ever cell value is in the same row but from column T ?
View 9 Replies
View Related
Jul 14, 2006
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.
View 2 Replies
View Related
Feb 28, 2007
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
CommVolArray = Array("UCA", "UGA")
CommAssArray = Array("LAR", "CAR","DAR","EAR")
Application. ScreenUpdating = False ..................
View 9 Replies
View Related
Jul 22, 2007
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).
View 2 Replies
View Related
Apr 14, 2014
Please refer to attached file.
I need a VB Code to do the following. Column E,I,M,Q (every 4th column until column DU), have integer number as shown and search until "REPORT END".
I need to change the integer number to following alphabet.
9 = A
18 = B
20 = C
82 = D
83 = E
84 = F.....and so on until 93 =
Book1.xlsx‎
View 6 Replies
View Related
Jul 28, 2008
My find and replace code is going to all the tabs in my workbook, but I want it to stop in my current spreadsheet I am on. Here is my
View 14 Replies
View Related
Apr 12, 2012
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
[Code] ...........
View 3 Replies
View Related
Jun 27, 2013
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?
View 5 Replies
View Related
Apr 23, 2008
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.
Worksheets(1).Range("B2:B50").Replace What:="1.ValueAddedProcessing", Replacement:= _
"Sheet1!A1:A11", LookAt:=xlPart, SearchOrder:=xlByRows, _
MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
View 9 Replies
View Related
Apr 27, 2008
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.
View 9 Replies
View Related
