=IF(AND($P2="Yes",COUNTIF($L:$L,L2)>1,$Y2="Market Specific"),TRUE,FALSE)
But it is not doing exactly what I want it to, and I am struggling to work out what I need to change to get it to do so.
I want the formula to do is look at a three Columns of data, and to highlight Cells in Column Y when the following criteria are met:
The Row is active (determined by the "Yes" part), that the Row is "Market Specific" and that there is more than one Row where these Criteria are met (this is where I thought COUNTIF would work, but it's not).
I have 3 data series, which are each futures contracts. Each of these futures contracts (the one on the left expires soonest) has an expiration date and as that date approaches, one will need to replace it with a new futures contract (the data series immediately to its right) having a later expiration date. This process is called a ‘roll’. I am seeking to create a continuous data series, which will incorporate the appropriate ‘roll’ based on two rules. The rules are as follows:
1) Where the volume of the 2nd contract exceeds the volume of the 1st contract for 10 consecutive intervals, roll on that date. 2) Once the roll date has been identified, roll at the specific time interval on that date that has the highest aggregate volume (between the 1st and 2nd contract). I have laid out the original data in a tab entitled ‘original data stream’. This is how the data will appear at first. In the tab entitled ‘Roll Example’, I have shown what I would like to happen to the original data streams. You will notice that in this tab, the data series are the same as the previous tab, except that they are not aligned as before. The data series to the left ( cells B4: D808) expires ..................................
I have a value in cell B23. I C23 & C23 is blank and numbers 0 to 18 is in cells E23 to U23I. I need VBA code that hightlights the row up to the value as indicated in B23. For example: B34 contains 4, the code should highlight the two blank cells up to cell J23.
I am trying to set formula to change row colour when certain criterias are met the options must be availiable for all rows but Governed of one colum.
Eg. If Cell A1 = "Contacted" then row will change to colour Green, "Left message then row will change to amber "Not contacted" then row will change to Red "Do not contact" then row will change blue
If these can be set as drop down menu to save operator typing them that's even better.
Once one is set I can copy the formula into the remaining collums.
I have read other threads dealing with this issue, but none seem to help me! I have a workbook with two worksheets, both with the same number of rows in them. I want each cell in worksheet1 to be compared to the cell in the same row and column in worksheet2, and if the worksheet1 cell is greater in value then the cell should turn red. I have tried the previously mentioned tactic of selecting the whole column in worksheet2, assigning it a name, selecting the column in worksheet1, and applying conditional formating based on the name. However, the result is always that the whole column in worksheet1 turns red, regardless of whether the cells are greater in value or not.
When activating Autofilter in a wide table, the only way to determine which criteria field was selected is to try and identify which drop-down arrow has become blue. When working on a laptop, it is rather difficult to identify the difference between the black arrows and the blue ones. Is there a way to have a macro or event procedure that will cause the selected criteria field (or fields) change its background color (into yellow, for example)? This way, the yellow fields will "stand out"… No more searching...
I have a file with telemetry data for n individuals. For each individual I have data on a number of days, over the course of a couple of years. On any given day, I have from 1 to several "fixes" for an individual. Fixes range from 1 to 3, with 3 being the "best". Note that I do not necessarily have data for all individuals on a given day. What I want to do is extract the row with best fix for each individual, on each day for which I have data for that individual. If there is more than one best fix for an individual on a given day, then I want to choose the first best fix (temporally). Right now my data is set up with columns:
INDV DATE TIME FIX ....other attributes
INDV = unique code identifying the individual
The data is sorted by INDV, then by DATE, then by TIME.
I figured I could do this using nested IF statements, where "TRUE" would be added to a new column (e.g., HIGHFIX) in the row of the first highest fix on each day for each individual, but have not been successful in doing this. I've also tried conditional formatting. Again, no success here.
I need a macro that will highlight a row based on a cell criteria. I have used conditional formatting but there is a lot to format and has dramatically slowed the sheet down. However, I know nothing about macros/vbas.
E.g. If it states JI sent then I want the whole row to be highlighted light green.
App form needed = peach Waiting list = light blue Cancelled = red font strikethrough DNA = purple font strikethrough.
Also - it could well be that in the row it has App form received, JI Sent and DNA all in 1 go. So ideally I would want that row to be highlighted light green with purple font strikethrough.
Booking Status JI Status Date JI Sent Attendance Cancellation date Chargable / non chargable Cancellations Chargeable / non chargeable DNA Date taken Time taken Event Title Start Date Time
I have a large file of invoices, and some have been paid. The problem is, sometimes it is not paid against the correct invoice, so I have a lot of credit and debit that should offset each other. I want to go through the sheet and highlight all the rows that has a matching ID and an a 0 value when total amount column was added up to another row. I would like it so that it highlights a different color for every pair of offsetting amount, but the same color highlight is fine too.
I am trying to write a macro which highlights all the cells with any reference to other cell. The macro should highlight a cell with formula like = 3 * A25 but not highlight a cell with following formula. = 3 * 0.4535566. 'Hasformula' property is not useful here as it can't distinguish between above examples.
Here is what I have. 4 Worksheets. The first worksheet is a summary page. I have 350 personnel that are broken down into three different groups. So each group has it's own sheet. Here is what I need to accomplish. Results need to be posted on the summary sheet.
I need to compare cells B2 & D3 for each row on a worksheet and display the number of times they match on a worksheet. For example how many times does EP & EP match on a certain row. I need to compare cells B2 & D3 for each row on a worksheeet and display the number of times they don't match on a worksheet. For example how many times does EP & MP occur. I've attached an example for reference
I have data in cells that are separated by pipe characters. What I need to do is to highlight the cells that are not the same. For instance, if a cell contains xxx|xxx then the cell is not highlighted but if a cell contains mmm|mmm|xx then the cell needs to be highlighted.
Sheet1 Â A1xxx|xxx2rrr|rrr|rrr3ttt|ppp4OO|OO|OOO5zz|zz|zz|tz6aaaaa|aaaaa7jj|jj|jj|jj|jj|jj8yy|yy9ww|tf|mz
I have a workbook and trying to copy all the way down the workbook using F4 blanks etc. When I highlight the column I want to fill in its not highlighting all the cells.
I have a spreadsheet that I need a m acro for, that will look at cell C13, if it is a 0 (zero) then hightlight cell C1 yellow. And so on, if C14 is zero, highlight C2.. Also look at the cells below, cell D13, if it is a 0 (zero) then hightlight cell D1 yellow. And so on, if D14 is zero, highlight D2..
I'm after a formula (or similar) that if cell H1 has no data in then cells A1:G1 must be highlighted in a colour, considering A1:G1 has data in. And then when data is input into cell H1 then the highlight disappears. is this possible?
I am trying to create a special workbook for my asphalt project. After inputting all of the day's truck information, I want to add up the total tons and then for the program to highlight which truck puts the total tonnage at or slightly above 500 tons. I'm not sure if "isolating is the right term to describe what I wish to do or not, but my whole workbook will be based off of similar actions like what I'm requesting. Here is a portion of my sheet:
I have a sheet that I have item numbers in a column that i want to highlight and change color when they are not the same. I want the colors to be unique to each number. I am currently using conditional formatting (Format cells based on their values (3-color scale)). I works alright but not perfect.
I am still new to VBA. I am trying to use VBA to search given rows for percentages above 0.81%. When it finds cells with percentages 0.81 and above it will high light the cell with red.
How do you highlight a row when conditions in two cells of that row are met? For example, I have a spreadsheet with a STATUS column ($A) and a TYPE column ($E). When the status is "open" and the type is "1", I want the row to be green. But when the status changes to "closed" I want the row to be a different color or no color at all. How do I do this?
I need to figure out how to have the macro look for a specific word and then highlight the last two columns in that row of that word and then repeat the process throughout the spreadsheet. I think it gets me very close to what I'm looking for: ('average of submitttocomplete' is the word i'm looking for)
Dim lColumn As Long lColumn = Cells(Selection.Row, Columns.Count).End(xlToLeft).Column Range(Cells(Selection.Row, Selection.Column), Cells(Selection.Row, lColumn)).Select
I am trying to use a button, that when pressed it highlights certain cells in a row
I have the following code
Sub HiLite() With Range("C20:Q20") .FormatConditions.Delete If Range("C20").Interior.ColorIndex = 3 Then .Interior.ColorIndex = xlNone Else .Interior.ColorIndex = 3 End If End With End Sub
This works fine, but when i press the button again it clears the highlighted cells back to normal but any highlighted text etc or conditional formatting is deleted
Is there a way of returning to what was there before the first press of the button?
Just incase it is pressed accidently etc, as there will be many rows with the same button
I am trying to figure out how to highlight the row of cells within the column that contains the same date as the date in cell e:1 the date in cell e:1 changes daily because the formula says =today(). With that said, the column contains every date of the year so i would just like to be able to have the row, that contains the same date as the date in cell e:1, highlighted AUTOMATICALLY.
I have 3 worksheets A, B and C. Worksheet A and B have numbers in three columns. In worksheet C, column D, I would like a formula or conditional formatting which will search worksheet A for the number in column D and if it finds the same number as the one in the cell, it highlights the cell Green. I would also like it to search worksheet B and if the number is in worksheet B, highlight the cell Red. In no cases will the number be in both worksheets (A and B).
I am having an issue with a workbook, when the workbook opens it runs through each sheet and protects it (allowing the user to manipulate unlocked cells). For some reason one sheet will not allow me to do that (in some respect). I can click on a cell, it will show me the formula at the top but the cell wont highlight showing you which one is selected. I have to use the named range box (top left) to see which one is selected. All the pther sheets work fine. I have checked to make sure that they are not set to Locked (in the cell formatting).
the code that protects the sheets is....... Sub MyProtect()
For Each ws In Worksheets ws.Protect ActiveSheet.EnableSelection = xlUnlockedCells Next ws
I would like to highlight the rows from Range A1:A200 that contain the month of October. The dates in this range are written as January 1, 2010, February 5, 2009, etc. I guess I just do not understand how to extract parts of a date. I have part of a formula that works if I place the exact full date in the code but I only want to grab the month so that all the October dates are highlighted.
This is what I have:
Range(Range("A1:A200"), Range("A1").End(xlDown)).Select For Each cell In Selection If cell = October Then cell.EntireRow.Interior.ColorIndex = 36 'light yellow Next cell
I would like to do this in vba and not in conditional formatting.
