I have an table in an MS Excel 2010. The table has two columns. The first column is a name of a person (Col A), the second column is the marks that the person secured in an exam (Col B).
I am applying conditional formatting. If I choose the following wizard
Home > Conditional Formatting > Format all cells based on their values
I can color the Col B on a 3-color scale. This is exactly what I want. However, I want it for the entire row and not only the cell in Col B. I want the name also to be formatted in the same color as the marks.
Anyone knows how to do this?
I have already looked around a bit. The following came close to did not solve the particular problem that I am trying to.
- http://www.howtogeek.com/howto/45670/how-to-highlight-a-row-in-excel-using-conditional-formatting/
- Conditional Formatting Rows Based on Date
You're probably going to have to use VBA code for this.
Right click the worksheet label and select 'View Code'
Inside the code window, paste in the following code:
Sub RunMe()
Dim xRng As Range, xCell As Range
With Me
Set xRng = .Range(.Cells(2, 2), .Cells(.Rows.Count, 2).End(xlUp))
' Change the first '2' above to reflect the starting row of your data
For Each xCell In xRng
xCell.Offset(0, -1).Interior.Color = xCell.DisplayFormat.Interior.Color
Next xCell
End With
End Sub
Now every time you run the macro (Alt-F8, select macro), column A will be formatted with the conditional formatting assigned to column B.
If you want this process to be automatic, change:
Sub RunMe()
to something like:
Private Sub Worksheet_Activate()
' This will run the macro whenever the worksheet is selected
or you could assign the code to a keyboard shortcut or a command button etc.
If you would like the code to run every time the file is opened, saved closed etc, add the code instead to the ThisWorkbook code window (although you'd have to alter the code slightly as 'Me' is referencing the particular worksheet in which the code is placed).
