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 contain the same value then their rows get highlighted in the same color and c14,c4,c45 also gets their own color they have the same value in it.
help me with this please. thank you
8 Replies
- 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.- 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.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
- thank you Donald_Genes but what to do in case all of these c2, c3, and other are many in a big excel sheet let say the values that are the same and are many how will i click on each of them to ad them in the formulas yet am not even sure how many are they
- Simple highlight all the range of Column that contains C2to whatever c10000 and use same logical Analysis.. Haven't seen the data to know what you really saying
