Find Columns By Name And Highlight Them Certain Colors Based On Text In Another Column
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
ADVERTISEMENT
Aug 29, 2008
I'm trying to find some vba code that will find dups on my worksheet and highlight them in a special way: first dup = bright yellow, second dup = bright green, third dup = bright red, and if there is a forth, fifth or sixth dup, then just increment color number to next one, than next, etc...
I would like for the code to run as soon as there is a change on the worksheet (when I finish entering data in a row). I think I already have the right code to do that:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("E9:E10000")) Is Nothing Then
Call dupSearch
End If
End Sub
I tested it out, seems to work as it should.
My major problem is that I need the code to check column F9:F10000 and compare the cells all along that column. That column concatenates 3 other cells in that same row (lastname&firstname&employee#), so if I run my dupSearch on that column only, I should find only real dups, and not just 2 people with the same name.
I also need to make sure it doesn't find empty cells, thinking they are dups. I would like to highlight column A to E of the row that contains the dup, and not just the cell in column F.
I have tried many other codes that I have found on the forum, but none of them work right with what I'm trying to do, and I just don't know enough VBA to make the changes I need.
View 9 Replies
View Related
Jul 13, 2014
When I hide columns in a column chart the different fill colors I used on specific columns no longer show up with the colors I originally had. Is there a way to maintain the proper sequence of column fill colors even when some columns are hidden?
View 1 Replies
View Related
Jun 16, 2008
i'm trying to do is have a formula that when certain text is typed into column 'b' (fmc, nmcmc, etc... anyways..) when an airplane is 'fmc' its 'green' and i want the aircrafts tail number block in column 'a' to autofill in with a green color, and when its 'nmcmc, i want it to be red. when its 'bq' i want it to autofill in as yellow.
View 10 Replies
View Related
Dec 16, 2009
I am having a trouble in Excel sheet.My column A has a drop down list with text- possible, not possible, not required.Based on the text, i need to populate texts in columns B, C and D.
For example
Column A drop down selected is "possible"
then B coulmn should automatically populate "1-3"
C should populate with "3-5"
D should be "5-7"
I am using MS excel 2007.
View 9 Replies
View Related
Sep 28, 2013
I have a data base in which I classify cells in three colors (green, yellow, and red) on my own. I would like the first cell in each column to indicate if there is a red or yellow cell in it's column. Because red cells are more important then yellow cells, if there is a yellow and a red cell in the column, I would like the red to over ride the yellow and the first cell would indicate red.
So basically, if there is a red cell in the column, no matter what other colors there are, the first cell should show red. If there is only yellow or yellow and green cells, the first cell should show yellow. If there are only green cells or green cells and clear, the first cell should show clear.
View 1 Replies
View Related
Dec 6, 2006
I have to make a macheine cycle chart. I have a column containing Step Names and a column containing step time in whole seconds. I need to paste the step names across Row 1 and whole numbers counting to X down colum A. I then need to have each second represented by coloring the cell, for example, Step 1 is 1 sec. and Step 2 is 2 sec. if "Step 1" is in B1 I need B2 colored then for "Step 2" in C1 i need C3-4 highlighted and so on for a variable number of steps. This is my first question so I hope I was clear enough. With this much programed I will be able to make simple changes to tweak it to fit my needs.
View 2 Replies
View Related
Feb 19, 2014
I have a large workbook, with multiple sheets. I would like to be able to select a name from a drop down menu, and have the cell color change for all entries of that name in my other sheets. I can do it manually, but it would be much cleaner and easier to have Excel do it for me. I have looked at other answers here on this forum and know that if it can be done,
View 1 Replies
View Related
Apr 25, 2013
I have a macro that converts all my PDF Purchase Orders to a text file and inserts the data/text horizontally into another document. However because the PDF's or the text within the PDF can be fomatted differently (that is on different lines etc) it therefore imports the information and it looks mis-aligned.I have attached a simple spreadsheet showing some sample text as it is imported and then below this how it should look like, all in line.
The range where the highlighted text in red is, is variable (but say nothing more than a variant of 10 columns). The text can also be Uppercase or Lowercase.So, I was wondering if there is macro code to find the "text" on various rows/columns and align it all in another column?
View 2 Replies
View Related
Jul 10, 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.
Scenario 1-AIF 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…
Scenario 1-BIF the number of days difference is MORE than 7 days, highlight the cells in the "Vacation Ended" column and "Number of Days" column GREY..................................
View 2 Replies
View Related
Sep 17, 2013
I have rows that contain property identifiers and their owners. One property ID can have multiple owners. I would like to color the rows differently to show each unique property ID with their 1 or more property owners. I have attached a file showing what I would like (with fake data). In excel, I was able to figure out how to highlight the duplicate values, but it only does them in one color. I would like each property ID value to have it's own color - as I show in the attached file. In the file, I have value 1234 as one color, the 4546 values as another color and 2233 values as another.
View 3 Replies
View Related
Jun 12, 2014
I am using below code to highlight duplicates with different colors.
I want to change the range in B3:C2000
[Code] .....
View 3 Replies
View Related
Feb 21, 2014
I have a spreadsheet that contains thousands of rows and I need to highlight all the duplicates in Column B.
What I need though is one group o duplicates to be colored one color and then the next group of duplicates be colored a different color. I need just a 2 color banding.
My need for 2 colors is that the cells contain 9 digit numbers and there can be duplicate groups right next to each other and for the eye to distinguish a different grouping of cells I need that 2 color banding.
I have the below code that colors everything in one color. Can I be modified or a completely new code to do 2 colors?
Code:
Sub Dups()
Dim Rng As Range
Dim CL As Range
Set Rng = Range(Range("B1"), Range("B" & Rows.Count).End(xlUp))
For Each CL In Rng
If WorksheetFunction.CountIf(Rng, CL.Value) > 1 Then
CL.Interior.ColorIndex = 6
End If
Next CL
End Sub
View 1 Replies
View Related
Oct 10, 2007
Each cell in Range AL3..AL50000 have text (sentence - description of problem).
Cell AL1 has my search word (i.e. loose)
I would like to search range AL3..AL5000 for word "Loose" - if found highlight every cell where was found - Yellow.
If AL1 is empty - remove all highlights.
Here is what I have so far (patches of codes found in this forum)
Sub FindTextInCell()
Dim ws As Worksheet
Dim myText As String
myText = Sheets("detail_report").Range("AL1").Value
If myText = "" Then Exit Sub
Set Found = Range("AK2..AK56000").Find(What:=myText, LookIn:=xlValues, lookAt:=xlPart, MatchCase:=False)
If Not Found Is Nothing Then
Found.Interior.ColorIndex = 36
End If
End Sub
My code highlights only first found cell and if I type any other search word - highlight from the 1st search does not get removed.
View 9 Replies
View Related
Jan 2, 2013
This formula allows me to find the lowest value in column U where column N contains the text "NO".
{=MIN(IF($N$2:$N$10000="NO",$U$2:$U$10000))}
I want to add another condition so that the formula only returns the lowest value in column U where (i) column N contains the text "NO" and also (ii) column F contains the text "YES".
View 11 Replies
View Related
Oct 28, 2006
I have a column of information, and I want to find out of there are duplicates in it without alphabetizing it. Where there is a duplicate (say E2=E5), I want to check the adjoining column to see if the value of those fields are duplicates as well (does F2=F5). So, what formula would I put in D2 to see if the value of E2 has an equal anywhere in column E, and if any duplicates are found (say E10,E33, and E43), to check if F10,F33 and F43 equal F2 (the value adjoining the cell that was searched for).
to find duplicate entries in a database by using people's last names (in column E) and birthdays (in column F). Odds are if they have the same DOB and the same last name, we've found a duplicate.
View 9 Replies
View Related
Aug 28, 2007
I need to find a way to highlight duplicate records within a list. The records would first need to be compared by (1) the month (found in column A), (2) the account number (found in column B) and then (3) the commission type (found in column D).
I do not need the duplicates deleted just highlighted or better yet maybe have them identified as "Duplicates" in an empty column (such as column H).
View 9 Replies
View Related
Feb 15, 2013
I am wondering how I can highlight rows that contain the same text across selected columns (not all). For example, consider the following table:
ID#6527
Jay
yellow
dog
[Code]...
I want to focus on Columns B, C, and D. I would like rows 3 and 5 to be highlighted, since they share the same text across the target columns. I assume this can be done via a formula in Conditional Formatting, but I'm not sure.
View 3 Replies
View Related
Mar 19, 2014
I would like to highlight the rows in my spreadsheet where columns G and H both contain the text "Yes". I've tried conditional formatting and VBA but can't seem to get either to work for what I need.
View 3 Replies
View Related
Nov 8, 2012
I just want to colour duplicate values but want to do it with this Dictionary method
Code:
Sub highlight_Dups()
Dim cell As Range
Dim rng As Range
Dim dict As Dictionary
[Code]....
View 9 Replies
View Related
Dec 19, 2012
I would like to have Excel 2003 be able to highlight the row if there is data in certain columns. Specifically if there is a number greater than zero.
For example the following could work:
[CODE][=OR(LEN(H9),LEN(I9),LEN(K9),LEN(Q9),LEN(R9),LEN(S9),LEN(W9),LEN(X9),LEN(Y9),LEN(AB9),LEN(AD9))/CODE]
However, there are times when I need to add a column/criteria. Thus I would have to go into conditional format and redo all the rows in the worksheet. Is there a smarter way to do this?
I was hoping to provide a list of cell address from the header or better than that look for specific numbers in the header row and if there is a number greater than zero, then highlight the entire row.
There is no specific pattern as to which columns would trigger the requirement.
Also how do you keep the ranges from moving when copying across and down. Sometimes I run into that issue. Maybe highlight the entire row and then enter the conditional format?
View 9 Replies
View Related
Oct 8, 2011
I am trying to tell the spreadsheet that if column G *and* column V are both identical to another row's column G and V, then the entire row is a duplicate and to highlight and delete it. I also want to record a macro of this process to apply to all spreadsheets.
Here are the tutorials I am looking at, but whatever I am doing is not working the way it is working for them.
Conditional Formatting, find duplicates with multiple columns [URL]...
I am highlighting cell A2, and under Conditional Formatting, I select "Formula Is" =SUM(COUNTIF($G:$G,G2),COUNTIF($V:$V,G2))>1
Then I select the format as light orange text, but it is only highlighting A2....
That is as far as I have gotten, and I still do not know how to go about deleting the duplicate entries. I have also read there are issues with recording a macro when trying to find duplicates using more than one column in the formula, but I have not gotten that far so I can't test it.
As is likely evident in my formula and process, I am not very experienced with Excel.
View 2 Replies
View Related
Jun 21, 2014
I want to enter a unique ID into an input box which will tell which row that id is available and then it will again ask me which column the cell needs to be highlighted. Once i enter the data, it will then ask me what is the change in data (again via input box). i can then enter the change data and then it stops.
View 1 Replies
View Related
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
Jul 8, 2014
I've got a large Excel table that's full of names and e-mail addresses - it's a report of e-mails that merges some data from two systems we use; a registration form and a database. We want to clean up the data so it only lists folks who 'registered' (it's not always BOTH people listed - sometimes it's only one!) - and the best way to do that is to take the information from a specific column in each row, see if it exists elsewhere in the row and clear the information that doesn't match.
For example:
-A----------------B------------------C--------------------D--------------E------------------F-------------------G-
John ---------- Smith -------- js@email.com -------- Jane -------- Smith --------- js@email.com ------ John
Richard ------ McGee ---------j@email.com-----------Jim----------Samename ----jsn@email.com-------Rich
Mary-----------Ladyface ------ms@email.com--------Steve -------Smith ----------ss@email.com-------Steve
Ideally, for each row I'd like to search A and D for the string from column G. If it finds it, the cell and the two cells to the right are fine - but everything else is 'cleared' (not deleted).
So the above table would look like:
-A----------------B------------------C--------------------D--------------E------------------F-------------------G-
John ---------- Smith -------- js@email.com --------------------------------------------------------------- John
Richard ------ McGee ---------j@email.com-----------------------------------------------------------------Rich
-------------------------- -------------------------------Steve -------Smith ----------ss@email.com-------Steve
View 5 Replies
View Related
Oct 8, 2006
Attempting to hide columns (of cities) via VBA generates an error when that same city is reselected (either individually, or as part of the group) in the list box, upon clicking the 'Hide' button.
The error happens at this point: ...
View 3 Replies
View Related
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
View Related
Mar 9, 2013
I have a list of names in Column B (Starting at B5) with assignments to them in Column A. I want the people who receive the file, to enter their name in B1 exactly as it appears multiple times in sheet. And hope to use conditional formatting to highlight (change the back ground color) of each cell their name appears in.
I've used a number of formulas in the Conditional formatting including "=(ISNUMBER(MATCH($B5:$B100,$B$1,0)))", Countif's and "Not(isnumber)..." but can't find a formula that picks up the whole text.
View 3 Replies
View Related
Dec 7, 2012
I have a 14 column report with a dynamic range of rows. In Column A, there will only be one cell that contains the text "Added Sections:".
I need to highlight, 10 whole rows up to the 14th column, after the cell that contains "Added Sections:".
View 6 Replies
View Related
Apr 12, 2013
I'm having issues with Excel's 2010 conditional formatting. Seems easy to use, but I'm trying to highlight values based on 2 columns of numerical data. Example:
Column F:
6
6
14
Column L:
3
NA
17
I would like Column L to highlight values that are greater than Column F in green. If they are less than Column L then highlight them in red.
Seems I was able to do this with Excel 2003, but I don't understand the 2010 version.
View 3 Replies
View Related
