Data Validation And Conditional Formatting Using VBA
Jan 14, 2010
I am using Data Validation for my drop down list, using “List” and “source” =$A$132:$A$159 along with Conditional Formatting that changes different fill colors, depending on selection. Is there a way to use VBA with a command button that when selected would give the user a dialog box so that they could enter additional comment that would be added to existing list and would also be able to select a fill color for that new selection.
Below I have a recorded Marco to give an idea of what I would like to achieve, but of course using a input box of some sort to make selections of text and color
I have conditional formatting for a range of cells eg. =COUNTA(AC3,AD3,AE3,AH3) It is currently set so that cell A3 changes to Green if AC3 is populated, Red if AD is populated, Orange if AE3 populated & white if AH is populated.
AE3 has data validation (Yes or No).
I need AE3 to change to White if no is selected and Orange if Yes is selected.
I have column of cells, both of with have drop down menus to select information from. I have an adjacent cell to display an error based on conditions. Is there a way to have conditional formatting to apply to the cells with Data Validation to change the color, based on the error. I have tried using the Conditional Formatting options but it still does not change the color of them. I believe i have to use the formula option, but not exactly sure what to put, i know a if statement could work but unsure on how, there are only two conditions that i would need for it to apply, if the adjacent cell has an error, and when it does not. Is this possible?
On sheet 1 I enter data such as: Visit Date | Customer Name | Customer Number | Job Number | Product
On Sheet 2 i have a list of our 130 products which would be similar to '1130Tots Standard 24" ' I have made these as a list so we can choose from the list using data validation.
Now, out of the 130 products we have about 4 main different Catagories - Tots Direct, Tots RTS, Direct, RTS. - I could put what category they are in in a column next to the list on Sheet 2.
What i would like to happen is if e.g. a product is chosen from the drop down list, it could look in the cell in the column next to it and see what Catagory it is in and format the entire row on Sheet 1.
E.g. Direct changes colour to grey, Tots RTS to Pink, Tots Direct to Grey/Pink stripes, RTS stays standard no colour.
A bit of a development on a question i asked last week to cut out another stage. Currently people have to enter the product, then choose in the next column whether is is Direct, RTS, Tots Direct, Tots RTS then conditional formating will colour the rows. - Want to know if it is possible to do this all in 1 go by selecting the product from the list.
I have a pretty simple spreadsheet (because I'm not an expert on excel) to track temp staffing requirements each week over a year. It only has about 150 rows and a bunch of columns but when I try to make it bigger (add more rows so I can put about 70 staffing positions instead of 19) it will not save (cannot save all of the data and formatting). I have read on the microsoft site that there are limits to data formatting, but it describes over 2000 rows?
I have a table that is a list for validating values in a column .what I want to do is instead of getting an error message anf stopping if the entered value is not valid the cells become red.
Sheet 2 has my friend's team (12 players). Each, a dropdown list on the player of sheet 1.
When a player will be eleminated, i will manually change his background to red in the list of sheet 1.
I want the background of the sheet 2 related to the player eliminated to change to red as well at the same time... how to do it automatically, without having to put a conditionnal formatting on EACH cell in sheet 2 ?
Conditional formatting: If a cell contains a string, say "NCT" or "nct", conditional format shall work. The string may be with a date entered i.e. "05/05/2006 NCT"
Same said cell is currently set to only allow dates. How can i achieve that Data Validation: Can data validation be set to only allow a date, and/or "date NCT" as above?
I have a table. I want to apply conditional formatting to the entire table so that wherever a cell contains a dropdown list (validation list) the cell is formatted with a different colour.
I have tried to modify the examples here on the site, but can't seem to get it. Cell G1 has list data validation of 5 numbers,6 digits long. In the cells below G10:G500, these numbers are listed. However, in the G10:G500 range the numbers have 8 digits. The first 6 digits remain the same and only the last 2 change. Here is what I would like to see happen: When I chose a number from G1, I would like to highlight all the cells with the first 6 digits matching G1 in the range G10:G500.
Column H contains text data as follows down the rows: BIKE/CAR/TRUCK.
Column N contains text data that would be entered free text by user down the rows; however, if the data entered in column N is not contained in column H it would warn the data is not contained in column H. Example: BOAT is not found in column H. Note; it could contain all text in column H for that row or just one word in column H for that row.
The goal is to recognize the text entered in column N is not contained in column H, warn and change the color of the text in column N to red.
I attempted to use Data/Validation, and Conditional Formatting; none seem to have a contain function.
Is there a formula or VBA I may use or any thoughts?
I need someone to go through them assigning a building number, level and location to each of them. I am using drop down menus, the first choice is building and has the following options:
B1 B2 B3 B4A B4B B5 B7
Depending on the option selected there maybe different locations, i.e. B1 has the following available locations:
1-4 4-16 MER ALL KIT
Whereas B7 only has: ALL. The third column, level, operates in a similar way.
I have a spreadsheet with data in a table with order numbers in column A. Although each order is given a unique number, the data populates with multiple rows, one for each line of the order, all with the same order number depending on how many lines where on the order (which is generated via other software and cannot be altered). Therefore the data, when sorted by order number, appears in groups:
[URL] .....
Is it possible to conditional format each line in groups of the same order number to make it easier to differentiate between each individual order which will still work when the data is sorted or filtered?
On this particular formula but my colleagues have informed me that they require cells with no dates in to have no colour fill. Firstly, I have searched and tried myself with no success on stopping the below formulas from filling empty cells with red, they are as below;
I am trying to colour a whole row of data (in columns A through to AA) based on the numeric entry in column AB. The numeric entry is either 1, 2 or 3. I reckoned I needed to use the IF function, but as writing formula is not my strong point I am struggling to enter the correct data! The first row of data I wish to use is 3. I have tried the following but to no avail: =IF($AB3,1). =IF($AB3=1). =IF($AB3="1").
I am using the simple conditional format "Data Bar" to make a progress bar, going from 0-1 (0-100%), works fine .5 is halfway...ect. but what i would like it to do is to change to a different colour only when it reaches the 100% or 1 in this case so you can see quickly that this progress is complete.
On one of my excel spreadsheets I have someone's name and a drop down list of "subscribe" or "unsubscribed" in the next column over:
Name
Company Subscribed?
Name1 amazing company Unsubscribed
[Code] .......
I have their names in column A on another sheet and a load of contact details and details on subscription price etc. I want to be able to conditional format these rows of details according to if they are subscribed or not. I'm struggling to come up with a formula that works and the conditional formatting menu isn't working much.
I searched, but couldnt find a thread which covers a VBA code which covers writing a conditional format into cells and changes each time contents of the cell are refreshed. The conditions are:
If activecell.value < 0 Then .interior.colorindex = 3
If activecell.value >=0 Then .interior.colorindex = 4
and i want this to happen for a big range of cells (range(a1:k1500) So basically, once the macro is run(which has the code to insert the conditinal formating for all the cells), if the user makes any change to the cell(within the range), the color of the cell should change automatically(like Conditinoal formating works by default). Prior to this, i was doing my cell coloring using the 'for loop' approach, but that of course does not reflect once the macro has ended.
I would like to be able to change the color of fill in a cell if there are 3 occurrences in cells. Attached is a sample.
In the attachment, if the cells A2, D2, G2 all have a number "1" in them I would like cell K2 to have a color fill for the cell.
The same would be for the other cells, if B2, E2, H2 all have the number "1", K2 should have a fill in the cell as well, same as if C2, F2, I2 have a number "1", fill K2.
I only want the fill color to be in cell K2 if all 3 of the cells, ex: A2, D2, G2 have a "1" in them. Also, some of the other cells may have a number "1" in them but I would not want them counted unless it is 3 of the consecutive cells.
I am trying to conditionally format some data in cells A3:A6 (shade the cells) depending on whether a number in B1 is 1 (green), 2 or 3 (blue), or 4 (red).
I have an Excel workbook that has a data connection to a Sharepoint List. I have 4 columns showing the quaterly status of a project. I want to apply conditional formatting based on the value in the column. I have 5 possible choices so I have to use VBA code to accomplish this because of the limit of 3 option in Excel. I am using this
Private Sub Worksheet_Change(ByVal Target As Range) Dim rng As Range Set rng = Intersect(Target, Range("X:AA")) If rng Is Nothing Then Exit Sub Else Dim cl As Range For Each cl In rng Select Case cl.Text Case "1" cl.Interior.ColorIndex = 4 Case "2" cl.Interior.ColorIndex = 6 Case "3" cl.Interior.ColorIndex = 3 Case "4"...............................
I have 2 worksheets. One has locations with numbers, example:
275 Location 1 276 Location 2
I have all my locations on the second sheet, but in 2 different columns, listed with numbers only, example: 271 275 272 300
I have 2 scenarios I need help with.
1st: If any of the numbers on sheet 1 match the numbers in column 1 on sheet 2, highlight the number on sheet 1 in green If any of the numbers on sheet 1 match the numbers in column 2 on sheet 2, highlight the number on sheet 1 in red
2nd: If the number on sheet 2 matches any number on sheet 1, highlight green
I want these to apply to all the cells that have numbers (it could apply to all cells I guess as it should ignore it if it doesn't match, I would assume)
I have a spreadsheet where I have a column of dates and I want conditional formatting to highlight the cell red if the date is less than or equal to today but if the cell is blank to do nothing.
At the moment I have the following formatting applied using the "format only cells that contain" option I have cell value less than or equal to =TODAY() except that obviously highlights every cell red that doesn't contain a date. Is there another conditional format I can apply in addition to this that will not highlight the blank cells ?
I work with some stock index data and I would like to get rid of some dots and empty spaces etc. I used conditional formatting and changed the font color of all the values I need. When I try to sort them based on font color (in order to delete the empty spaces/dots) Excel does not do it. "Go to...conditional formatting" also doesn't work.
I'm running into a problem when trying to use INDIRECT in combination with Conditional Formatting Data Bars.
Basically I want to show a Data Bar in a certain cell based on the value from another cell.
It would be easy if I could simply copy the value from the other cell across into the cell in which I want to show the data Bar. Unfortunately I cannot do this as the values in the cells where I want to show the Data Bars can change.
As a solution when using Conditional Formatting Data Bars I figured out I can simply do the following: > Under 'Minimum' use 'Type': Number with 'Value': Zero. > Under 'Maximum' use 'Type': Formula with 'Value': =INDIRECT("B'&ROW()) This will pick up the value up from column B in the same row. > In column B i then have this formula: =A1/C1. In C1 i have the percentage that the Data Bar should be using. By dividing the value in A1 with this percentage B1 will return a value that is used for the "Maximum" setting which will then ensure the Data Bar will display correctly.
For example: If A1 value is 4 and C1 value is 25%. Then the formula in B1 will return value 16. The scale used for the Data Bar is then 0 to 16. Seeing the value in A1 is 4 the Data Bar will correctly show 25%. If I then change the value in A1 to say 2 the scale used will change to 0 to 8 and the Data Bar will still be showing 25%, which is exactly what I'm after. So effectively this means a value from another cell is used to work out the Data Bar.
So all is fine up until this point. The problem arises when I want to apply this same logic to the rest of the column.
If I change the 'Applies To' to the range I want it applied to all the "Maximum" will only be based on the top row value. I'd expect cause I'm using the INDIRECT reference with ROW() it would have picked up the values from each individual row. I could create a new CF rule for every row, but that's not practical as there are way too many rows.
Why does the INDIRECT function with ROW() not work in this case? How to make the CF rule copy down properly?
