Highlight Row Range Based On Value In Another Column
Mar 13, 2008
I have been reading and searching on how to get around Excel's limit of 3(or 4) conditional formatting rules with VBA.
I have a pretty good grasp of what the code is telling the system to do, however what I am not understanding is how can I have the Code check multiple sets of columns?
ex Columns A,B and C Rows 1:100 need to be checked if they are between a particular range and clolums D,E, and F need to be checked against a completely different set of criteria.
I tried cutting and pasting the Sub, changing the Range values and the conditions, but it always errors out.
View 5 Replies
ADVERTISEMENT
Dec 7, 2008
I'm really struggling to achieve the following:
I have 75 rows and 10 columns populated with numbers (assume its A1 to J75 but it could be anywhere).
Firstly, if any of the numbers in a column are > 10 then I want to highlight all of the numbers in the column (even those
View 9 Replies
View Related
Jan 16, 2014
I have a spreadsheet with month names in column A10 and down and want to highlight the row based off a cell(which will be A3) so if cell A3 = Nov it would highlight all rows that have Nov in column A. I don't want to highlight the entire row just columns A.
View 5 Replies
View Related
Aug 23, 2007
I need VBA help. I know how to highlight every other row but what I can't do is highlight a row if column values are the same. For example I have 5 columns but only the first one has the data I'm interested in. If the data in the first column looks something like this:
1
2
3
4
4
5
5
5
6
6
6
7
.
.
I want to highlight every other row that has similar values therefore I want to highlight row 2, both row 4s, and three 6s...etc.
I also want it to start on the 2nd row of every page until the last row of every page which is row 37 in my case.
View 9 Replies
View Related
Feb 25, 2008
I am attempting to have a cell highlighted if the adjacent cells are visible, but no highlighting when the cells are hidden. For example, Cell D1 is highlighted when cells A1:C1 are visible, but not when cells A1:C1 are hidden. (this is for the group/outline feature)
View 3 Replies
View Related
May 29, 2012
I am trying to write a procedure to highlight entire rows in VBA based on a entry in column A.
I have the below which works for say 1000 rows but breaksdown when I have 58,000 rows which is the usual amount of data I will have.
Below is the code I have so far. It appears when I debug it breaks on the red line with type mismatch.
Option Explicit
Sub RemoveBH()
Application.ScreenUpdating = False
Dim intcount As Long
For intcount = Cells(1, 1).CurrentRegion.Rows.Count To 1 Step -1
[code].....
View 7 Replies
View Related
Aug 26, 2013
I would like to highlight the cells in Column A the have numbers in them. Starting with that number shift down based on the number that many rows and highlight them.
HTMLSheet1 *AB154020842*4020843*4020844*4020845*4020846*4020847*40208483
5010909*50109010*50109011*50109012*50109013*50109014*501090153
50112016*50112017*50112018*50112019*50112020*50112021*501120222
50206523*50206524*50206525*50206526*50206527*50206528*50206529450212430*50212431*50212432*502124
View 3 Replies
View Related
Jun 9, 2009
I want to highlight an entire column based on whether a cell in that column is equal to today's date.
I know how to write a loop that cycles through all of the columns until it finds today's date and then modify the column's properties, I was wondering if there was an easier way to do it. Also, I want the highlighting to be temporary meaning it should not save.
View 9 Replies
View Related
Aug 4, 2014
Abc500
abc-500
bbb300
bbb250
cac100
cac-100
ddd200
ddd300
fff-500
I want to be able to highlight data in column B where amount is identical (ignore negative sign) only where data in column A is identical.
In this particular case it should highlight 500,-500 where data in column A = ABC & also 100,-100 where data in column A =CAC.
View 2 Replies
View Related
Jul 9, 2009
I have a worksheet with 20+ columns. For this macro, I only need to focus on 4 of them. However, none of these columns are ever in a fixed position so the macro would need to find them by name and NOT by column position. Here they are...
1. Vacation Type (will only have a text value of either "Cold" or "Warm")
2. Vacation Started (will always have a date *x/xx/xxxx)
3. Vacation Ended (sometimes it will have a date '*x/xx/xxxx' and sometimes it will NOT have a date and will be truly blank)
4. Number of Days (currently has ALL truly blank cells)
THIS WHOLE MACRO SHOULD NOT BE CASE SENSITIVE ANYWHERE
Here's what I would like the macro to do...
Scenario 1 - for "Cold" values
Find "Cold" text values in the "Vacation Type" column
"Cold" values WITH a date in the "Vacation Ended" columnIF there IS a date in the "Vacation Ended" column in the same row, put the number of days difference between the "Vacation Started" column and "Vacation Ended" column in the "Number of Days" column.
The amount of days in the "Number of Days" column will determine whether these cells should be highlighted GREY or RED.
A) IF the number of days difference is 7 days or less, highlight the cells in the "Vacation Ended" column and "Number of Days" column RED.
OR..............................
View 12 Replies
View Related
Jul 28, 2014
need to understand the easiest way of highlighting the highest and lowest number in a range of cells several times in the same column.
As I said the cell range will be in the same column. So highlight the highest number green and lowest number red in cells a3 to a9 and then repeat the same process again in cells a11 to a17 and so on.the cell references are an example.
So I have several separate sets of numbers in the same column.
View 3 Replies
View Related
Feb 1, 2012
Let's assume Cell A2, A3, A4, etc, only contain names/text.
Then let's assume I have set up cell B2, B3, B4, etc, to contain a percentage value based on a formula and some other entries I have made.
It should be simple, but I'm having trouble - how do I set up A2, A3, A4, etc, to highlight into different colours based on Column B's data/percentage falling into a certain range?
ie: - if B2 is between 0% - 25%, then highlight A2 green
- if B2 is between 26% - 50%, then highlight A2 orange
- if B2 is between 51% - 100%, then highlight A2 red
View 2 Replies
View Related
Apr 10, 2013
I am trying to calculate some averages. What I have is 3 columns of data in A, B, C, also the "tasks" in A are in named ranges ex: "Award Contract" is a named range - "Task_Award" and "Confirm Updates" is a named range - "Task_Updates". I've attached a sample excel sheet.
I'd like to be able to create a macro to evaluate column A, and for every row in range "Task_Award", give me the average of the corresponding cells in column C and put it in the same range of cells in column B , then, for every row in "Task_Confirm" then give me the average of the same range of cells in column C and place the result in the same range of cells in column B. This is my very first post so I hope I am doing this correctly. I have 77 of these task ranges to evaluate and it will take a long time to do it manually. I'm thinking of a loop function.
View 1 Replies
View Related
Jun 1, 2009
I'm trying to write a code that calculates 2nd column based on 3rd column's number or vice versa based on the condition set on the 1st column. Below, there are two procedures. "SimpleCalc" and "SimpleCalc2". I first wrote SimpleCalc2, but it isn't working, so I worked around the error by writing SimpleCalc, which selects a cell and moves around by offset. I personally find it hard to read and error prone as I develop more logic around it.
I'm trying to develop more function based on this code, so I need to make it neat and flexible. what I'm doing wrong in SimpleCalc2? Or do you have any suggestion to improve the code "SimpleCalc"? I'm using Excel 2003.
Sub SimpleCalc()
Dim SimpleMethodRng, SimpleMethod As String, i As Integer
ActiveWorkbook.Worksheets("Dashboard").Range("P5").Select
SimpleMethodRng = ActiveWorkbook.Worksheets("Dashboard").Range("N5:P12")
For i = 1 To 8
SimpleMethod = SimpleMethodRng(i, 1).........
View 3 Replies
View Related
Aug 7, 2009
in column B of sheet1 i have every 3rd-4th row a name. i need to copy the name and the the data on the row below the name to sheet2,
the list of names can be min 2 max 30.
so sheet 1 has row 14- name in column B and C. in row 15 in columns d:z data for the name.
i need to copy this to one row in sheet 2 copied for each name up to 30.
View 9 Replies
View Related
May 1, 2012
I have the following data beginning in cell A1:
Loc P1 P2 P3 P4 P5
AA 3 4 5 6
AB 2 7 8
AC 2 7 8
AD 4 5
If I choose to sum Loc AA - AC, I would like my result to look like this:
Loc P1 P2 P3 P4 P5
AA- AC 5 9 11 13 14
The values in the Loc column are in sequential order. There may be times when I will only need to sum rows AA - AB or AB - AD.
View 3 Replies
View Related
Aug 12, 2008
I have defined my last row and column by using:
lr = Sheets("week1").Cells(Rows.Count, "A").End(xlUp).Row
LC = Sheets("week1").Cells(1, Columns.Count).End(xlToLeft).Column
Now how do I select the range starting at A2 through the last used column ( LC ) through the last row ( LR )?
View 9 Replies
View Related
Sep 5, 2013
I need formatting to highlight the dates in Column H if they are a greater than a week or more from Column G. Tried some different ways of doing this with the conditional formatting but cannot get it to work yet
View 12 Replies
View Related
Mar 17, 2014
Trying to compare a list of names in 2 columns. Column A has a short list of names I'm looking for in the long list of Column B.
How can you create the macro that does...
If any value in Column A matches any value in Column B
Then format (bold, highlight, etc...)
View 1 Replies
View Related
Jun 1, 2012
I have a spreadsheet in which column "c" represents a department: 01, 02, 03, etc. every time this value changes I want to do a subtotal. The code I've come up with thus far is:
Sub Subtotal()
Dim lrow As Long
For lrow = Cells(Cells.Rows.Count, "c").End(xlUp).Row To 2 Step -1
if cells(lrow, "c") cells(lrow-1, "c" then range
Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(4, 5, 6), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
ActiveSheet.Outline.ShowLevels RowLevels:=2
End Sub
But, I can't figure out how to get set my range to include all the data that pertains to a department to do the subtotal.
View 3 Replies
View Related
Jul 31, 2014
I want to refer to columns by column order (i.e. A=1, B=2, etc.)
I want to sum a range for dynamic columns and fixed widths (i.e. B2:B5 or F2:F5, etc).
i have C_N as variant and i am trying to write the formula but desperately getting syntax error.
Code:
Range("M5") = WorksheetFunction.Sum(Range(C_N& "2" : C_N&"5")))
i tried before to make another easier formula like
Code:
=Range("N5:N" & LastRow)
and it worked.
View 2 Replies
View Related
Jul 26, 2006
I would like to name a range based on a row number and a column number. I have found the row and column number using this code, but I can't seem to get the rest right.
Sub LastRowCol()
Dim intLastRow As Integer
Dim intLastCol As Integer
Dim ws As Worksheet
For Each ws In Worksheets
intLastRow = ws.Cells.SpecialCells(xlCellTypeLastCell).Row
intLastCol = ws.Cells.SpecialCells(xlCellTypeLastCell).Column
View 3 Replies
View Related
Mar 19, 2008
Im trying to do a sumif reading the name in column N (either "Business" or "Procurement"), and suming all values in the range AW:AY.
this is what I have come up with....
sumif(N10:N49, "Business", AW10:AY49)
but it returns incorrect values and not the whole range????
Any ideas? Auto Merged Post Until 24 Hrs Passes;here is an example of what im trying to do...
H2 and H3 are my attempted formulas
View 3 Replies
View Related
Jun 25, 2014
Formula to highlight a cell in a column when compared to each value in another value. If I have the columns
A B
1 1
1 2
2 3
3 5
4 7
7 8
I want the values in column A to be highlighted if the excist in column B, both of the number 1:s.
View 4 Replies
View Related
Feb 24, 2009
I am trying to write a formula with some variables passed into it. i want to sum up part of a column based on a date range (i've got the range already).
View 3 Replies
View Related
Nov 18, 2013
setup:
Columns: c)6:00 AM, d)6:15 AM, e)6:30 AM
Rows: 4)tech1 Phone, 5)Tech2 Phone, 6)Tech3 Phone
What I am tying to do is to select the column that matches the time, for instance anything before 6 AM would select Column C, betweeb 6 and 6:14 still selects column C, 6:15 to 6:29 selects column D, Etc
View 2 Replies
View Related
Dec 30, 2007
I have a worksheet that has hourly data. So Column C of this worksheet looks like
1
1
1
1
2
2
2
3
3
3
3
3
3
What I want to do is, given an hour h, automatically create a named range that contains all the rows such that the value of Column C is equal to h.
View 9 Replies
View Related
Mar 9, 2008
I want to define a dynamic named range based on the last date in a range (AE4 down). Unfortunately there are gaps in this range where no date is in a cell.
View 5 Replies
View Related
Jun 15, 2008
i have a table in excel with 36 rows and 36 columns i.e. 36x36=1296 individual cells. Columns and rows have headers/labels i.e. 36 rows with row label headings and similarly 36 columns with column label headings. I would like to automatically create unique cell names for the 1296 cells based on where the columns and rows intersect using the row and column labels. The cell name format I would like to create is [ColumnLabel_RowLabel].
I've manually created the cell names in the attached file as an example of what i would am seeking (i've greyed the cells that I've done this for).
View 9 Replies
View Related
Jul 21, 2009
i have a sheet for monitoring sickness. i have used a macro before which automatically selects a range and emails it to recipients when i press a button. what i would like to do is have excel automatically choose the range for me based on today's day. in the attached example i would like column "A" copied along with the 5 columns before today's date, today's date column and also the 5 columns after today's date. paste the lot into an email and send. (i have highlghted the parts i would like copied and pasted into an email based on today's date (21st july).
View 4 Replies
View Related
