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?
View 3 Replies
I would like to highlight cells is two conditions are met:Cell = 0Offset(0,-1)>0I tried the conditional format wizard and entered a formula: =IF(AND($J2=0,$I2>0)) But I keep receiving formual errors, which I understand, because it appears to be incomplete formula. But I am not sure what else I need to add to the formula in the conditional format wizard
View 2 Replies View RelatedSo I need highlighting certain cells. I have a sheet that keeps track of people's points over a series of races to determine who will make the "team". The team is selected one at a time based on the most points, starting with the leftmost column and working right. What I would like to do is highlight the highest score in each column (which I know how to do) but then tell the sheet to ignore all the other numbers in that same row. That way if the person with the most points in column one (M in my sheet) also has the most in column two (N in my sheet) Excel will ignore that number in the second column and highlight the next highest number.
I'll attach my file so there is a visual reference : ODN Points.xlsx‎
I have a spreadsheet of actions/tasks. I have a column with the expected completion dates of each action and another column stating whether the action/task is "open" or "closed". I would like to highlight any actions that have gone past their expected completion date and are marked as still being "open". Obviously i dont want any row that are closed to be highlighted. I know the =TODAY()- B1>0 formula will highlight the dates cell that have expired but how do i extend this formula to what i require.
View 5 Replies View RelatedI want to highlight A1 red if C1 is greater than 55. Is this possible?
View 3 Replies View RelatedI have a spreadrsheet (sample attached) which populates a calender due to columns on seperate sheets being filled with text and corresponding dates. I started using conditional formatting to highlight certain conditions but soon realised I need more than 3 so I tried looking into VBA - and failed.
All the cells in the calender have the 3 usual conditions associated to them but in addition I need to be able to colour individual cells if they contain certain phrases or parts of phrases like "BP" or "Current"
I am seeking assistance with code for column H to be highlighted when I have a value of "Pending" or "Work In Progress" is present in column E?
View 3 Replies View RelatedI am having a problem finding or creating a macro for what i am needing to do. I have a workbook with 2 worksheets (2 months july and august) to figure out a billing process.
I need to be able to First of all search each value in Column A on sheet 2 (august) to Column A on sheet1 (July).
My sheet is set up like this where ID is a buyer and Products 1,2,3...are if they have purchased that product from us
A B C D
1 ID Product_1 Product_2 Product_3
2 100 0 0 1
3 200 1 1 0
4 300 1 0 0
5 400 0 1 0
Better Picture representation attached
Both worksheets are very similar but August may have some changes from July such as new ID's and changed prduct purchases. What I need to do is have each ID in Column A be compared to Column A in the July sheet, but it may not always be the same cell (because of new ID's added they are moved around)..so it need to search for the same ID first then compare. Once it finds the ID, I then need it to compare the rest of the row (the product info) for that ID to see if any products were added or changed to determine billing. And for any changes that are found in the comparison, I need them to be highlighted.
Now in the case that a new ID was added...it wont be in the July sheet, so the whole row would need to be highlighted to notify of a new ID and new billing needs to be added.
This way the billing person can look at the sheet, see the highlited changes and see what is new or needs to be added to a previous ID's billing. Currnelty they are looking at it manually to see if there are changes.
In column A, I need to highlight the cells green if the value is less than < the values in columns B and C, yellow if A = either B and C, and red if A is greater than B and C. But I only want the formatting to stay in column A. I'm using Excel 2010 and Windows 7.
A
B
C
Green->
444
512
482
Yellow->
364
571
364
Red->
101
99
87
I’m trying to change color in cell B9 according to different conditions in Cell C9 (than on b10 according to c10 and so long until necessary - probably will be around 2000 lines). Since there are more than 3 conditions and in two cases condition depends on the color of the C Column, I can not use conditional formatting. I’ve searched and found similar forums here, but since I’m ignorant in VBA code, I couldn’t manage to make adjustments. So if you can help me with the code, I would really appreciate it!
Condition and Results required would be:
IF column C = “S” than on Column B = color cell light blue with white border
IF column C = “P” than on Column B = color cell Green with white border
IF column C = “A” than on Column B = color cell Yellow with white border
IF column C = “L” than on Column B = color cell Red with white border
IF column C = “C” than on Column B = color cell Dark Blue with white border
IF column C = “ ” than on Column B = color cell white
IF column C = “V” and has white background than on Column B = color cell white
IF column C = “V” and has a Dark Grey background than on Column B = color cell Dark Grey
I don’t know if the last two are feasible.
1. I want to highlight cells on a form so that a user knows which cells to fill out.
2. When they print the form I do not want the cell shading to print.
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.
View 3 Replies View RelatedI have a lottery pool in sheet 1 in which i have the names of the people and their number picks. In sheet 2 i have the lottery results.
All i want is to be able to highlite(adding a backround color in the cell) to the matching numbers in sheet 1.
I am running a macro to make some changes to certain cells in a spreadsheet (formula changes)
Is there a code I can include within the macro so that all cells that change are also highlighted in yellow?
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.
View 9 Replies View RelatedI 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..
With a range of cells (a1:ao41) I want to color several cells depending on the value of that particular cell and another color when that particular and a corresponding cell have both the same value. It's some sort of sociogram The first row and column both contains names of students. Let say the students have to choose the three most populair ones to work with and the three most unpopulair ones. Positive ones get the color blue, negative ones get the color yellow. That's easy to do with conditional formatting. But now comes the tricky part. If for instance Student 3 chooses Student 7 and Student 7 chooses Student 3 the color of both cells have to be green if both positive and red if both negative. That's also do-able with conditional formatting, but it most be much easier to do with VBA. I just don't know how .
View 5 Replies View RelatedI have found several forums with this question but none of them seem work/fit my needs
What I need to do is look in Sheet1 at column 'B' begining at row '15' (row 14 is table header) and if the cell contains "Yes" then copy 3 cell directly to the right (C15:E15 in the case of the first row) into Sheet2 into cells B15:D15 then do the same for row 16 and so on up to row 600
Creating a cut down copy of the Sheet1 which does not contain the row which determines if it should be copied.
I have a spreadsheet which I need more than 3 conditions to the formatting - is there anyway of doing this in VBA!!! Its for a holiday chart that i'm doing - some of the items they can enter into one cell is: B (Bank Holiday) / 1 (1 days holiday) / TR ( Training day) / SL (Study Leave) / 0.5 (half day hols) and more.
View 2 Replies View RelatedI have a Macro that when run checks for the lowest value in a line & highlights that line. It works just as it should, infact it works better than I had planded. When I update a cell value and move to the next cell it changes the highlight.
Option Explicit
Sub SELECT_LOW_PRICE()
Range("a4:i126").Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, _
Formula1:="=and(a4=min($a4:$i4),not(isblank(a4)))"
Selection.FormatConditions(1).Interior.ColorIndex = 6
Cells(1, 1) = "Updated " & Now
Cells(1, 1).Select
End Sub
I am getting a "Run-time error:'13' Type Mismatch" when we try to paste a value (in this case "Y" or "N") in multiple cells at once. I did look up this error on the microsoft site and found this. http://support.microsoft.com/kb/821292. which didn't seem to be the exact issue I was having because I am not using a macro.
Sub Worksheet_Change(ByVal Target As Range)
Dim icolor As Integer
Call ChangeRowColor
If Not Intersect(Target, Range("$A:$E")) Is Nothing Then
Select Case Target
Case "y", "Y"
icolor = 4
Case "n", "N"
icolor = 3
Case "?"
icolor = 6
Case "n/a", "N/A"
icolor = 13................
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?
View 8 Replies View RelatedI 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:
Truck #Tonnage
1 21.93
2 22.84
3 23.21
4 24.89
5 24.8
6 25.01
7 24.88
8 24.44
9 23.14
10 24.53
11 22.03
12 22.61
13 24.9
14 24.69
15 24.85
16 24.81
17 24.82
18 22.55
19 22.18
20 24.52
21 22.49
22 24.81
And so on.....
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.
condition formatting.PNG
Is there a way that when a cell is selected it will highlight the cell and all of the cells along the same row?
View 12 Replies View RelatedI 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.
Here is what I have done so far ...
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
[Code]...
=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 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
