One colour per row only

[Tigerexcel]

I have a list with 3 columns, each row is allowed one cell to have a background fill eg yellow. Is there a way to change the particular coloured cell in a row to another column and then have the original cell revert to standard fill background. In this particular exercise I need to ensure that no row has more than one background coloured cell.

 

[GraH]

Hi,
Can you show with some sample data?
I'm thinking you need VBA to accomplish this.
Perhaps tag you post if still possible.

 

[Tigerexcel]

Hi G.

	Model A	Model B	Model C
Jane	66	96	56
Ken	75	63	65
Fred	63	84	52
Mary	81	54	93

Cells which have background coloured red cells are 66, 63, 52, 54. I need to be able to change a row eg Jane to say Model B so that it has a red background fill but the 66 becomes a cell with a normal background. No person (row) can have more than one background coloured cells at any one time.

 

[GraH]

I came up with this if you don't mind using a column to set the highlight selection.
It's possible with VBA to simply click on the cell to colour it and remove background from the others. I'm sure the active and fine coders here can provide it.

Book1
	A	B	C	D	E
1		Model A	Model B	Model C	Highlight
2	Jane	66	96	56	a
3	Ken	75	63	65	b
4	Fred	63	84	52	c
5	Mary	81	54	93	a
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:D5	Expression	=MATCH($E2;RIGHT($B$1:$D$1;1))=COLUMN()-1	text	NO

 

[mikerickson]

You could put this in the sheet's code module.
Double clicking a cell in columns B:D will change its fill to red. Only one of those cells will be red. (Double clicking on a red cell will make it white)

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    With Target
        If 2 <= .Column And .Column <= 4 Then
            Cancel = (.Interior.Color = vbRed)
            .EntireRow.Range("B1:D1").Interior.ColorIndex = xlNone
            If Not Cancel Then .Interior.Color = vbRed
            Cancel = True
        End If
    End With
End Sub

 

[Tigerexcel]

You could put this in the sheet's code module.
Double clicking a cell in columns B:D will change its fill to red. Only one of those cells will be red. (Double clicking on a red cell will make it white)

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    With Target
        If 2 <= .Column And .Column <= 4 Then
            Cancel = (.Interior.Color = vbRed)
            .EntireRow.Range("B1:D1").Interior.ColorIndex = xlNone
            If Not Cancel Then .Interior.Color = vbRed
            Cancel = True
        End If
    End With
End Sub

Hi Mike,

I have tried this but when I double-click, it just launches edit mode.

 

[mikerickson]

Did you put it in the sheet's code module or a normal code module?

 

[Tigerexcel]

Did you put it in the sheet's code module or a normal code module?

I tried it in both and couldn't get it to work. I opened a new workbook with some new data and it works fine.
Just one other question, will this work in a Pivot table? Once you double-click PTs open another worksheet with the underlying data.

 

[mikerickson]

Will this work on a Pivot Table? Probably.

 

[Tigerexcel]

Will this work on a Pivot Table? Probably.

Does anyone know of a way to stop the pivot table from opening another worksheet everytime you double-click while using this macro?