Forum Discussion
Excel Highlight a row according to a change in a specific cell
am using Excel 2016 and i want an advice on how to do this: i have an excel table and i want to highlight each row basing on a change in a cell in row and change a color for example if c2,c3,c7 cont...
Let me give you a tip that would guide you..
* Select the range of data you want to be highlighted (don't include column headers)
* Open the conditional formatting dialog and add a new rule based on a formula.
*Make sure you find out your unique Values *
* =$c2,$c3,$c7 ="same value" (Based on certain criterias;
* Hit OK and change the formatting as desired.
* You can make another rule to handle the other color, or just create new rows filled with red for example; the conditional formatting will override the color based on your entry.
* Select the range of data you want to be highlighted (don't include column headers)
* Open the conditional formatting dialog and add a new rule based on a formula.
*Make sure you find out your unique Values *
* =$c2,$c3,$c7 ="same value" (Based on certain criterias;
* Hit OK and change the formatting as desired.
* You can make another rule to handle the other color, or just create new rows filled with red for example; the conditional formatting will override the color based on your entry.
I have a table in an Excel worksheet where the rows are generally grouped (perhaps sorted) by the value in one column. In the below example, it is Column A and it is sorted by Year. But it's not necessarily numeric and it's not necessarily sorted; it could be "Apple", "Apple", "Apple", "Pear", "Banana", "Banana".
So the fill for the rows with 1999 in the Year column would be one color, say orange, then when the value changes, the fill color changes. It would be fine if the color just alternated, say orange then green then orange, etc. I'm interested in a general way of doing it, not something that relies on the column being years or a number, it could be a car make, or a fruit, etc. Also, if there's another year 1999 many rows down, it need not be fill color 1, it just has to be different from the non-1999 rows adjacent to it.
I've used conditional formatting for several things but I can't get it to do this. The purpose is to be able to better see when the year changes. This is different from just alternating the fill.
So the fill for the rows with 1999 in the Year column would be one color, say orange, then when the value changes, the fill color changes. It would be fine if the color just alternated, say orange then green then orange, etc. I'm interested in a general way of doing it, not something that relies on the column being years or a number, it could be a car make, or a fruit, etc. Also, if there's another year 1999 many rows down, it need not be fill color 1, it just has to be different from the non-1999 rows adjacent to it.
I've used conditional formatting for several things but I can't get it to do this. The purpose is to be able to better see when the year changes. This is different from just alternating the fill.
Here is a macro:
Sub ColorRows() Dim d As Object Dim r As Long Dim m As Long Dim v As Variant Dim c As Long Application.ScreenUpdating = False Set d = CreateObject("Scripting.Dictionary") m = Range("A" & Rows.Count).End(xlUp).Row For r = 2 To m v = Range("A" & r).Value If Not d.exists(v) Then d(v) = RGB(224 * Rnd + 32, 224 * Rnd + 32, 224 * Rnd + 32) End If Range("A" & r).EntireRow.Interior.Color = d(v) Next r Application.ScreenUpdating = True End Sub- To make sense of what you said, create us a fake data not necessarily your real data but something similar so we can all collectively help you. Cos personally I like to talk to the data itself rather than doing much explanation
