I have a spreadsheet that requires me to manually hunt down records that match based on several different criteria. this is very time consuming and inefficient. i would like to find a way to highlight certain records that match based on several different fields. i have posted an example spreadsheet that also has a more detailed description of what i am trying to do; which is located in the second sheet.
1. Where there is data in column B 2. Goto first row of data 2. If column J = "Closed" 3. then select Cells from column B to J in that row and colour these cells in red 4. If column J= "Open" 5. the select Celss from column B to J in that row and colour these cells in grey 4. Repeat for next row until you get to cell in column B where there is no data.
Recieve run time error 1004: Method 'RAnge of object_GLobal failed
=============================================================== Sub Colourclosed() Sheets("Risks").Select Dim LastRow As Long Dim i As Long LastRow = Range("B" & Rows.Count).End(xlUp).Row For i = 8 To LastRow If Range("J" & i).Value = "Closed" Then Range("B", "J" & i).Select Selection.Interior.ColorIndex = 3
Note: I've managed to do it for one cell ie for Column B by replacing "If Range("J" & i).Value = "Closed" Then Range("B", "J" & i).Select with "If Range("J" & i).Value = "Closed" Then Range("B" & i).Select" This works but only colours in cell in column B, how do i do this so it colours range of cells
I'm trying to keep track of training dates for employees.
I have my spreadsheet conditionally formatted to highlight dates according to months, based on if the training date is due: next month, this month, last month, or 2 months ago; all different colors.
I used "Format only cells that contain" and then used "Dates occurring." The spreadsheet worked perfectly until this month; evidently it doesn't recognize Jan of 2010 being "Next Month," so those cells aren't highlighted. Current formatting is custom, mmm-yy. I tried different date formats but it didn't change anything.
When I input in cell D3 with 1 and E3 with 7 or below, the cell of E3 will be red color. when I input in cell D3 with 1 and E3 with 8 and above, the cell of E3 will be green. When I input in cell D3 with 2 and E3 with 15 and below, the cell of E3 will be red color. when I input in cell D3 with 2 and E3 with 16 and above the cell of E3 will be green.
What I am looking to do is format a row so that it is highlighted if one cell in that row meets one of two criteria or if another cell in that row meets one of two criteria: I was hoping to use the excel conditional formating tool but I'm stumped as I need 4 criteria and it can only do 3 criteria.
The cells in question require a greater than or less than criteria. If could use something like: Formular is =$AJ16 <= -0.11 or =$AJ16 >= 0.11 and Formular is =$AK16 <= -0.11 or =$AK16 >= 0.11 I would be very happy but it won't let me do this - is there a way it can be done?
I have conditionally formatted a column of staff names where duplicates are highlighted - this is fine but there are some duplicates that I do not want to include - these all have the same criteria in that they include the suffix (v)
I have five fields that have a conditional format applied to them: (see attached). Columns J, K, L, N and O. These conditionals highlight if a minimum number is entered. Ex. Minimum pushups for a 30 y/o male is 27, if a 26 is entered it highlights red. The total score (where I need this to perform) is calculated in column Q. It will format red if the total score is below 75 but what I can't figure out is how to make it format if any of the previous minimums have not been met.
Ex. In cell L4, his crunches were 25 (a automatic failure -- red formatting). Now the total score is above 75 so it calculates as passing (green), but I need it to highlight red regardless because of the minimum not met in cell L4.
Find the most common occurring string in my spreadsheet based on a lookup value. For example, if my table is as follows:
- I want to calculate the most common value chosen by user 234 (A in this case), user 524 (B in this case)... and so on. - I want to calculate the percent of the chosen value. So in the previous point, A was chosen ~67% of the time by user 234... and so on.
I'm not sure which formula to use. I am able to find the most common occurring value in column B using the formula below (which I found here), but how can I do that for a specific value, a user ID in this case?
Each question is multiple choice (either A/B/C/D/E) and the values listed are the probability of each letter occurring. What formula would I need to put in the 'output' column for it in each row to output a letter based on the probabilities. E.g. in question 1, most of the time it would output E, but sometimes (rarely) A and very rarely B/C/D.
I want to be able to change the font color of the numbers in a column based on even or odd. I selected the columns and formated them to all be blue... thats the color I want for even, but now I can't figure out how to set a condition that checks for odd numbers and changes them to red. I now have a count of even/odd for all records, and even/odd for the last 30, this provided some very interesting results.
i have a spreadsheet which is used to keep the training records for the company it counts no of staff and their skill levels for different parts of the job, when a person moves to a different branch the skill required changes and they have a set period of time to aquire the new skill.
when the time is up the colour of the old skill is changed to a red font by a conditional format based on the date.this is then counted by a calculation so the team know how many people are to be targeted for extra training.
the problem i have is the calculation will not count conditionally formated cells, so whenever i update the sheet i have to go to each cell with red text and change the font colour manually to red, this sounds really silly and is impossible to explain to anyone who does not understand conditional formatting. i e the people who will end up using the database
what im looking for is a way to count conditionally formatted cells based on the condition being met i.e. they are showing as red font.
I have a row of cells pertaining to jobs B3 across to I3.
In B3, C3, D3, E3 the value entered is text. In F3, G3, H3, I3 the value entered is time.
What I am trying to do is to change the Background colour to Red on cells F3, G3, H3, I3 if either H3 or I3 or are empty at the end of the current day but only if there is some text in any or all of the cells B3 - E3
So for example if H3 or I3 have no time put in them by 23:59 today, when the workbook is opened tomorrow the cells background colour will turn red.
Not sure if this if poossible or not? hopefully I have explained it well enough but if not pease let me know.
I have the following code. I am trying to conditional format a row based on the value of the A column of the row.I have a total of 3 rows. I have to use VBA because in time I may have 4 rows of data. The problem is I after the first do while loop the code stops and the code doesn't proceed to format row 2.
Im having some issues with some code Im working on. My goal is to delete the rows that do not have conditions met. Conditional formatting is used in 6 columns of my spreadsheet and the cell fill is to turn red when below .50 . This code is shown below. What I need to do now is look at the 6 columns where the conditions are applied, and delete any rows where the column cells are not red. To further clarify, if in row 1 column X:X does not meet criteria, but column AL:AL does, the row would not be deleted. However if none of the columns meet the criteria, the entire row is deleted.
With the click of a toolbar button, I've got my vba inserting a new tab, renaming it, inserting headers and now working on the code to insert all the formulas needed in 12 columns to copy down as far as there is data.
The 4th column D is the hardest. Currently, I'm using manual "Conditional Formatting" to colorize the cells in Col D but need the VBA to place that "Conditional Formatting" code into the sheet so that when the sheet is created, it will hold the conditional formatting feature.
Specifically, the formulas I'm using in the Conditional format wizard are:
Formula: =$B2$D2 then turn the cell red Applies to: =$D$2:D
Copy down (in other words, the colorizing should work all the way down Column D as long as there is data in Col D.
I have a spreadsheet with due dates in column X. I want to create conditional formatting so that cells turn red when the due date is less than or equal to 30 days away, but turn gray when the due date has passed.
I've accomplished the first step by using the following: =AND($X6
I'm afraid as its a multiple conditional format and thus needs coding. At the moment I'm using the following
Private Sub Worksheet_Change(ByVal Target As Range) ' Conditional Formatting for more than 3 conditions Dim rng As Range ' Target is a range::therefore,it can be more than one cell ' For example,,someone could delete the contents of a range, ' or someone could enter an array.. Set rng = Intersect(Target, Range("E27:E34,G27:G34,I27:I34,K27:K34,M27:M34,O27:O34,Q27:Q34,S27:S34,U27:U34,W27:W34,Y27:Y34,AA27:AA34"))
If rng Is Nothing Then Exit Sub Else.....................
This works very nicely, so that for example if I type the number 10 into cell M33, that cells fill colour becomes black (and the text turns white). Alas I would now also like to extend the fill and text colour to the cell one to the left too. To take this example I would type 10 into cell M33 and cells L33 and M33 would both be filled black and their text would be white. I'm not sure if this requires a change to the code, or if its possible to just have a formula or something which copies the fomatting of one cell into another
I have a monthly calender, with each month on its own sheet. I have a sheet named Holidays, which list the holidays. I have been able to use conditional format and highlight the dates in the months the holidays fall on, but I would like to highlight a range of cells below the date on monthly sheet. I have attached a sample of what I have and would like to achieve. I am not much good at using VBA, but would not be against using it either.
Is it possible to format a cell based upon the format of another cell? For instance, if the cell to the left of a target cell is bold, is there a conditional formatting cell that can be used so that the target cell is bold as well?