I have a long list of users and the hours they have accumulated over the past year of use. I have used a Conditional Format to find the top 10 users. I want to create a chart of just those 10 users so I was hoping there is a way to pull the value and cell of the users name by only pulling the cell values that show up in green.
Is there a way to have excel basically find the formatted cells and list the values? Or are there other ways to list just the top 10? These top 10 users is dynamic and can change throughout the year so the top 10 now, may not be the top 10 tomorrow.
Here is my entire excel spreadsheet... its to hard to split up since there is so much data being pulled from the HoursChart for the year tab.
Top 10 users.xlsx
Click on "Top 10 users" Tab, there you can see I have all the users listed and their hours from the sheet prior. I also have a condtional format in place on hte values.... I need a way to either pull those values into another cell or I need to chart only the cells that have the conditional format... however I need the names and the departments along with them.
I have a Sheet ( Named "Summary" for Example ) of about 4,000 Rows that has a LOT of Conditional Formatting.
I Added Another 100 Rows this Morning and when I Tried Saving it a Message Saying that Not All the Formatting for the New Data that had Been Added had Been Saved. Is there a Macro or Something I can Run that will Make the Conditionally Formatted Cells Stay the Colour that they are but Delete the Conditional Formatting Part of it Achieved Using "Format" & "Conditional Format" from the Menu Please.
Ideally I would like to be Able to Enter the Number of Rows ( From Row ? to Row ? ) that I want this to Apply to.
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.
I have a range which has conditional formats based on other cells. I want to copy this range into another sheet and retain the current formats as fixed formats without copying the conditional formulae. ideally in VBA.
I need to run a loop through a column of values (attachment col B) and when it finds a "J" it will apply conditional formatting to a row of 4 cells directly adjacent. The attachment is a theoretical before & after.
I'm trying to use this Find Method and combine it with a countif or loop. Something that will count a number of occurences of a unique type of character. I'm looking to find all "F" characters in Bold, Italic and Size 16. Here's my find code that I'm trying to use. I can get it to work by itself but not along with a countif or loop.
Sub count_4() Dim r As Range Set r = Range("A1:A6") With Application.FindFormat.Font .Bold = True .Italic = True .Size = 16 End With r.Find(What:="F", LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _ SearchDirection:=xlNext, searchformat:=True, MatchCase:=True).Activate End Sub
I have a tab called SAP DUMP where we copy our raw data to, from here we manually copy and paste into another sheet called ORDER TRACKING SHEET. When we do a fresh dump (overwrite) of raw data into the SAP DUMP tap we need to see if the there is a new entry which needs to be copied across to the ORDER TRACKING SHEET. I've got a vlookup to show this. The one I can't work out is that on the ORDER TRACKING SHEET I also need to see if there are any entries are now no longer on the SAP DUMP tab. This is where I thought a condition format might work, color the line red if it's no longer on the SAP DUMP tap. Can't make it work.....
I have a workbook with 12 worksheets corresponding to months. Each is formatted in A1 with the month (Date, Jan-01) and all rows below, starting at A5, are the days of the month (Custom, ddd d). All cells are linked so they change accordingly with A1.
I'm trying to apply conditional formatting so that all Fridays will have light grey shading across the row. I can get it to shade every 7th row using the MOD & ROW functions, but the shaded row changes with subsequent months and years (not necessarily Fridays). Can I get it to recognize every Friday through the year.
Disregard the First Table. I cant remove it. refer to the Second Table
This table is a Task Tracker. The Start Button Adds the Current Date in Enercon's Row C1 and the row below (C2) will automaticaly filled with color using Conditional Formating. My Question is how can I code a conditional format for Trail1 Rows. For example when Trail1 Cell is active then you click the start Button instead of Coloring D2 Cell it should Color D3 Cell.
Here is the Code in Start Button
Sub cmdStart_Click() Dim i As Integer Results = MsgBox("Are you working on " & ActiveCell.Value & " today ?", vbOKCancel, "") If Results = vbOK Then
With Sheets("Sheet1") i = .Cells(2, Columns.Count).End(xlToLeft).Column + 1 .Cells(2, i).Value = Now() i = i + 1
End With Else Range("A1").Select End If End Sub Regards, Kevin
I have a table where Sales are in Column B and a % calculation is in Column E. I want to highlight the cells in green that are over 40% and that also meet the criteria where the sale in that row is above $100.
In conditional formatting I tried to use =and(b4>100,e4>40%) but it did not do anything. I am also okay with using a nested if statement.
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 m creating a sheet in excel where I need to do some conditional formatting but need more than 3 rules so i'm guessing i'll need to use some vb code.
Basically I have a range of cells say from c12 to ag15 These cells all have formulas in linking to other cells in other sheets.
Tha values that will be in them are as follows: s, h, hd,ooo,z and maybe 1 or 2 more will be added later. I need the cells background and text colour both to change to a colour depending on the value in the cell.
eg. cell value = s then background and text colour both to be yellow.
I'm trying to set up a conditional format using a two way lookup. The chart is 15 colums across and perhaps 500 down. Index / Match functions do find the correct value - BUT - the challenge is there ARE duplicate values in either the column or row that are "all" getting highlighted with the conditional format (color a cell).
I want to apply conditional formatting in a cell based on the contents of another cell. I can do it if I just want it based off 1 condition (=b151="sample") but would like to if it met 1 of a couple possibilities.
For example, cell H151 I would like to fill green if cell B151 contained the text "sample" or "example". B151 has a list box with about 10 possibilities but I only want a few of those to turn H151 green.
Is there a way for a macro to capture the CURRENT colorindex value of a cell that has conditional formats? It seems to capture the default colorindex rather than the one that is currently displayed based on the conditions. I need to capture the current one.
In column C, I have a list of phone numbers of some 10,000 items. In column F I have 50 telephone numbers. I need a formula or a conditional formatting solution that will allow me to mark/indicate/color those numbers in column C that are in column F.
I have column in which all of the different types of deposits for the day are listed. I also have additional columns listing the types of the deposits along with the subtotal for the specific type i.e cash, credit card etc. I would like the subtotal in one columm to be the same color along with all of the individual deposits in the first column that are included in the deposit.
I'm trying to conditional format a cell that contains a formula that when the formula returns anything other than "" the cell contains a border. My formula within the cell is referencing another worksheet =IF(Sheet1!A1="","",Sheet1!A1)
Ideally the formula within the conditional format would be =NOT(ISBLANK(Sheet1!$A$1)) but it isn't possible to reference another worksheet within a conditional format.