Modifying Code To Reference Cells And Its Formatting?
Aug 7, 2012
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]....
View 9 Replies
ADVERTISEMENT
Jun 28, 2006
Basically my code works fine if I enter a number into the reference cell. But for my actual application, I need it to update the reference cell's background color when I enter other data. B21 has a =IF(COUNT(B18, B19, B20) < 3,"",B18 * B19 * B20) statement. So as I modify B18, B19, B20 it should update B21's background color. Here is the VBA:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B21")) Is Nothing Then
If Target < Worksheets("OEE Data").Range("K6").Value Then
Target.Interior.Color = vbRed
ElseIf Target >= Worksheets("OEE Data").Range("K6") And Target < Worksheets("OEE Data").Range("L6") Then
Target.Interior.Color = vbYellow
ElseIf Target >= Worksheets("OEE Data").Range("L6") Then
Target.Interior.Color = vbGreen
Else
Target.Interior.Color = vbWhite
End If
End If
End Sub
Obviously my VBA isn't sound and hence won't apply any background to B21. If I just change it to AA1 and enter the criteria into AA1, it's background changes without problem. The reason I need this in VBA is because in the cFormatting I need the first condition to be 'is equal' ="" (white background) or it defaults my BLANK CELL to being > 1 (Green background). This is why I need 4 conditional statements.
View 2 Replies
View Related
Feb 16, 2009
This code works great for me, but I need some help modifying it. This code searches for data in the whole sheet, two things I would like to do is limit the search range to one column and then to find the whole string, not partials, for example, find 331 without finding all the 33's or 31's.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim MyRange As Range
If Target.Address = "$A$1" Then
Set MyRange = Cells.Find(what:=Target.Value, After:=Target)
If Not MyRange Is Nothing Then MyRange.Select
End If
End Sub
View 9 Replies
View Related
May 24, 2008
I am trying to modify the below code in order for it to:
1) Check the cells in Column A to find any blank cells and zero values. If the macro finds a blank cell or a zero then delete the entire row.
2) Check the cells in Column D for any blank cells and "N/A" (this N/A is text, it is not an excel error). If the macro finds a blank cell or "N/A" then delete the entire row.
Here is the old code
Sub DL()
Dim lastrow As Long, i As Long
With ActiveSheet
On Error Resume Next
.Columns(3).SpecialCells(xlCellTypeFormulas, xlErrors).EntireRow.Delete
On Error GoTo 0
lastrow = .Cells(Rows.Count, 3).End(xlUp).Row
For i = lastrow To 1 Step -1
If .Range("C" & i).Value = 0 And .Range("D" & i).Value = 0 Then .Rows(i).Delete
If .Range("C" & i).Value = "N/A" Then .Rows(i).Delete
Next i
End With
End Sub
Here is the modified code...
Sub DL()
Dim lastrow As Long, i As Long
With ActiveSheet
On Error Resume Next
.Columns(3).SpecialCells(xlCellTypeFormulas, xlErrors).EntireRow.Delete
On Error GoTo 0
lastrow = .Cells(Rows.Count, 3).End(xlUp).Row
For i = lastrow To 1 Step -1
If .Range("A" & i).Value = 0 And .Range("A" & i).Value = "" Then .Rows(i).Delete
If .Range("D" & i).Value = "" and .Rang("D" & i).Value ="N/A" Then .Rows(i).Delete
Next i
End With
End Sub
1) Is this the correct and efficient way to accomplish the task?
2) How to i change this in order to select a specific sheet?
View 9 Replies
View Related
Nov 23, 2007
The following code is a is designed to paste into "Y1", the value in column 5 of the active row when that cell is changed. This works perfectly if I select the value from a dropdown list and hit enter, or if I enter a value manually and hit the right or left arrow key. However if I manually change the value of the cell and then hit enter, it does not work because it looks for the value in the next row.
Does anyone have any idea what I can do to make the macro stay focused on the cell who's value changed even if I hit enter?
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim AR As Long 'Active Row
AR = ActiveCell.Row
If Target.Column = 5 Then
ThisRow = Target.Row
If ActiveCell.Value > 0 Then
Selection.Copy
View 9 Replies
View Related
Dec 4, 2008
The following codes work well but when I have changed the following condtion ,
If cel = Tgt.Range("A2") Then
to
If cel "" Then
I got worng result, can anybody help me to correct it?
Option Explicit
Sub test()
Dim sh As Worksheet, Tgt As Worksheet, i As Long, j As Long
Dim d, a
Dim c As Range, Rng As Range, cel As Range
Set Tgt = Sheets("Report")
Set d = CreateObject("Scripting.Dictionary")
j = 0
On Error Resume Next ...............
View 9 Replies
View Related
Mar 19, 2013
modifying the following VBA code; this code auto-generates 4-digit unique numbers, using zero as one of the starting digit as well. I need the 4-digit numbers NOT to begin with a zero, the 4-digit numbers should only begin with numbers 1-9.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cell As Range
Dim Test As Integer
[Code]....
View 2 Replies
View Related
Jan 27, 2014
I am trying to make a find and replace macro for multiple items.
Please see the attached file for further explanation : macro.xls
View 3 Replies
View Related
Jul 24, 2014
I'm using conditional formatting where one column of cells is referencing another column of cells. For example, I want cell K2 to highlight if the date entered doesn't match the date in F2. I then want cell K3 to highlight if the date entered doesn't match the date in F3. I want cell K4 to highlight if the date entered doesn't match the date in F4 and so on. I'm not having any issue putting conditional formatting on the cells/row references individually, but when I try to copy the formatting (by using format painter) to save time all of the cells in column K just reference the first cell in column F since the first cell in column K references that F cell but not any other F cell. How do I copy these row by row references between columns so that each reference is unique?
View 1 Replies
View Related
Dec 21, 2008
As shown in the image given below, I have 2 tables. In Image 1, Vacation details are being fetched from a different worksheets. There can be 13 possible values in a particular cell. These 13 entries are,
V
SL
CO
T
PH
W
ML
PL
AD
BE
CTW
EM
LOP
MAR
OTR
Excel Jeanie HTMLSheet2 *BCDEFGHIJKLMNOP2Feb-09*Feb-093MonTueWedThuFriSatSun*MonTueWedThuFriSatSun4**************15VSLCOTPH***23456786WMLPLADBE***91011121314157EMLOPMAROTR****161718192021228CTW*******232425262728*9*************** Excel tables to the web >> Excel Jeanie HTML 4
WHAT I NEED :
If the value in cell B5 is V then J5 is being colored with PINK background. Similarly if any cell in Image 1 has value SL ( for e.g. C5 ) then K5 is being colored with orange. Similarly there should be 13 different types of color coding for all the above values which I mentioned.
WHY I NEED THIS ?
As you can see in Image 1, If I apply these colors to Table 1 only, then the user will not be able to see the value V or SL or any values belong to which date ?
Note - I am doing this for the whole year. But if someone can send me VBA code then I can do it for the rest.
View 9 Replies
View Related
Sep 26, 2006
I'm using Conditional Formatting for an entire column to check the value of each cell and compare it to the value of a cell in the same row but a different column. (Cell Value equals =$D2) It works great until I insert or delete a cell in the formatted column. The reference does not change as one would expect. I've played around with formulae such as =$D2<>$P2 but the reference only changes for the P2, not the D2. I've also tried using =CELL("contents", ADDRESS(Row(),4)) but this causes excel to complain.
View 3 Replies
View Related
May 27, 2013
I do not have excel or an xls for a sample, I will try and restate the question.
In Cell A1 It will be used for copying the contents of and pasting the completed information into web browsers.
In Cell A1 You will find This information contained and formatted in one cell alone.
Figure-1
A1=
Information About Company
Brief Story About Company
Product Name: (X)
UPC Number: (Y)
Serial Number: (Z)
Terms Of Sale
Etc
End Figure-1
I want to essential Modify (X)(Y)(Z) using different cells one for each variable. "All completed information will be found inside of A1"
Having Cell B1-B9 Being used as labels "Product Name" "UPC Number" "etc" and using cell C1-C9 to input variables for = (X)(Y)(Z)(ETC)
In reality from cell A1 will change dynamically, but some information will need to stay constant.
Every thing In B column will just be labels.
Every thing In C will be for variables.
View 1 Replies
View Related
Jan 6, 2014
I am building a spreadsheet that references 'LHA rent rates' listed in other cells.
Example table
Bed rate Code: LHA per week:
0 - £55.00
1 - £91.85
2 - £109.61
(Ie two columns with six cells. The first number before the dash are in a separate column. Each of these £ amounts will be in a separate cell.)
In another part of the sheet, i want to be able to type '0' in Cell 1, and this will trigger a formula in Cell 2 to retrieve the correct £ amount i.e., £55.00 in the 'example table' above. Likewise if i change Cell 1 to '2', then Cell 2 automatically updates to £109.61.
I think this would be a long IF formula in Cell 2, but how to do it.
View 3 Replies
View Related
Jul 25, 2013
Below is my current code, what I would like to add is once the user presses Yes it also verifies that "Sheet TEOD" has all cells filled out in the Range("E6:E14,E17:E28,E32:E35,E39:E51"). I just want the code to stop and say that "Sheet TEOD" not complete. Otherwise if there is something in that range it verifies the next sheet which is already there and sends.
View 1 Replies
View Related
Sep 7, 2006
I have to convert a range reference in the form of
Range(Cells(1,1),Cells(2,2))
to a reference in the form of
"A1:B2"
View 5 Replies
View Related
Jun 11, 2014
I have developed the following code to colour cells dependent on reference to adjacent criteria (in Col D).
Is there anyway I can simplify this code to avoid three separate "For each" statements?
Code:
For Each c In Range("E12", Range("e" & lastrow))
If c = "" And c.Offset(, -1) "" Then
c.Interior.ColorIndex = 7
[Code] ..........
View 2 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 31, 2012
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] ............
View 3 Replies
View Related
Jun 11, 2008
I have 5 columns set up: A,B,C,D,E
D is the sum of A and B
E is the sum of A,B,and C
As I add in a new column to the right of C (call it C2), I need D (which has shifted over one) to sum A,B, and C.
I also need E (which has also shifted over one) to sum A,B,C, and C2.
Essentially I need a function in a cell that will be able to reference two cells to the left even as more cells are added.
View 9 Replies
View Related
Feb 15, 2010
I have lets say 12 months of data. I have formulas that reference the latest 6 months. When I insert a new column to input a new month, how can I make the formulas include the new months without manually updating them.
EXAMPLE:
12 months of data exist in cells B3:M3 going from B3(oldest) to M3(newest). Formulas reference latest 6 months of data in cells H3:M3. When a new month hits, I insert a column after column M.I would like the formulas to now reference cells I3:N3 which is now the newest 6 months.
View 14 Replies
View Related
Jul 14, 2014
Is it possible to reference a cells value to define a range reference?
[Code] ......
I am trying to define the row value in the range reference with a value in a secondary cell?
View 3 Replies
View Related
Jun 3, 2014
i want to calculate percentage between A and B and highlight top 10 values in B.
as of now i am calculating % in C and applied conditional formatting in C. filtering C based on color i am formatting B manually.
is there any option to avoid this manual work.
Total countAlarm reported%
562545%
783545%
322578%
View 3 Replies
View Related
Feb 28, 2013
I have a row of data I want to be blacked out if there isnt a reference number in the first column
So if I type the reference number i can in theory then use the rest of the row but if there is no reference number in the first column i cant see any of the row ...
View 1 Replies
View Related
Jun 29, 2006
I have made a sample file with conditional formatting. The file contains 3 sheets which contain a number of orders. Conditional formatting is changing colour of the whole line depending on value in column C. The formatting contains reference to a certain cell in column C. It is very easy to make when the lists are so short: I make conditional formatting for line 2 with reference to cell C2, copy formats in the other 2 lines and change formula references to C3 and C4. However in the original spreadsheet there might be more than 5000 lines per sheet and it will be too time consuming to correct the formulas manually. Does anyone know how I can make a reference to column C without specifying each particular cell?
View 3 Replies
View Related
Sep 11, 2009
I need to create conditional formatting on a worksheet where I need to change the cell colour based on 6 cases.
The VBA that I have found does not work with referenced cells to another worksheet in the workbook which I need as all the data is referenced with calculations.
I also need to specify the cells that the VBA will apply to as I need to apply 6 or 7 different different sets of conditional formatting on the same worksheet to different groups of cells.
View 14 Replies
View Related
Aug 28, 2009
I have a bunch of images which I want to be able to display in different cells depending on certain values or conditions. Say for example you have a drop down box with a list of fruits. When you pick a fruit, it displays its picture. Is this possible? If all of the pictures exist somewhere on the sheet, can you reference the pictures to have them appear in a cell rather than having images always "floating" over the sheet?
View 3 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
Nov 6, 2008
I am copying some data from one workbook and pasting the data into another workbook depending on certain criteria (using autofilter) using excel VBA. For only One column i need to multiply data by 100 (i.e. convert it into percentage) For example column A in source worksheet has all values and i will paste it in column D of target worksheet. But before pasting i need to multiply the data by 100 so column D in target worksheet has values in %.
View 3 Replies
View Related
Jan 24, 2012
I need a way to take the below example and combine each entry into 1 line.
ALRSItem Number/QuantityStockingDescription On HandUnit Actual Cost170PC1832PCB1un43.75circuit de PC1832 nouvelle version 5010 (P832)Avg. Unit Value:43.750000Costing Method: Moving AverageCosting Unit:un250282un2.05Braquette pour contact anti-vandale 112 ADEMCOAvg. Unit Value:1.025000Costing Method: Moving AverageCosting Unit:un
I would like to take each part and place the specific part information provided into 1 line for each part number.
View 1 Replies
View Related
Aug 8, 2007
It's refreshing to know that to make VBA even more lightning fast it doesn't check the For limiting variable. /sarcasm mode off
Does the following surprise anyone? Surprises me. (And yes, I know what an Exit For is. This code is designed solely to illustrate the point.)
Sub JimmyLoopVar()
Dim i As Long, K As Long
K = 6
For i = 0 To K
If (i > 3) Then K = 2
Debug.Print i; ; K
Next
End Sub
So the loop runs 7 times regardless of K! (At least on XL03 )
View 9 Replies
View Related
