CELL COLOR

dennisdjones · May 21, 2002

Have existing Row colors from a code am using from "Mudface"

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Count > 1 Then Exit Sub
If UCase(Target) = "e" Then Target.EntireRow.Interior.ColorIndex = 3
If UCase(Target) = "E" Then Target.EntireRow.Interior.ColorIndex = 3
If UCase(Target) = "u" Then Target.EntireRow.Interior.ColorIndex = 8
If UCase(Target) = "U" Then Target.EntireRow.Interior.ColorIndex = 8
If UCase(Target) = "p" Then Target.EntireRow.Interior.ColorIndex = 4
If UCase(Target) = "P" Then Target.EntireRow.Interior.ColorIndex = 4
If UCase(Target) = "w" Then Target.EntireRow.Interior.ColorIndex = 6
If UCase(Target) = "W" Then Target.EntireRow.Interior.ColorIndex = 6
If UCase(Target) = "O" Then Target.EntireRow.Interior.ColorIndex = 46
If UCase(Target) = "o" Then Target.EntireRow.Interior.ColorIndex = 46
If UCase(Target) = "a" Then Target.EntireRow.Interior.ColorIndex = 2
If UCase(Target) = "A" Then Target.EntireRow.Interior.ColorIndex = 2

End Sub

Have tried some of the traveling color codes, but they clear the existing color formats as they cross that row.

Anymore ideas would be a great plus.

Thanks

dennisdjones
This message was edited by dennisdjones on 2002-08-12 10:47

NateO · May 21, 2002

There are two ways of doing this in the thread attached. If your selection is in a colored row using the second method, you'll loose pre-exisiting interior colors. The first method clears all interior colors on the entire sheet.

They are both worksheet event procedures, you'll want to right click on the sheet in question, select view code, and paste one of the procedures in this sheet code module

http://www.mrexcel.com/board/viewtopic.php?topic=3988&forum=2

Hope this helps.

Russell Hauf · May 21, 2002

You can use either the Worksheet_SelectionChange or Workbook_SheetSelectionChange events. Something like this:

Code:

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    Sh.Cells.Interior.ColorIndex = xlNone
    If Target.Cells.Count = 1 Then
        With Rows(Target.Row).Interior
            .ColorIndex = 6
            .Pattern = xlSolid
        End With
    End If
End Sub

(If you use the Worksheet_SelectionChange event, just change sh.cells to cells).

Hope this helps,

rh

verluc · May 21, 2002

Code:
On 2002-05-21 12:16, Russell Hauf wrote:
You can use either the Worksheet_SelectionChange or Workbook_SheetSelectionChange events. Something like this:

Code:

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range) Sh.Cells.Interior.ColorIndex = xlNone If Target.Cells.Count = 1 Then With Rows(Target.Row).Interior .ColorIndex = 6 .Pattern = xlSolid End With End If End Sub

(If you use the Worksheet_SelectionChange event, just change sh.cells to cells).

Hope this helps,

rh

Hi,

Two questions :

1.Is it possible that the color-row ended by the last used column and not to column 256

2.I have several colors in my sheet and do not want to delete them.
Many thanks for your time

verluc · May 21, 2002

Code:
On 2002-05-21 12:56, verluc wrote:

On 2002-05-21 12:16, Russell Hauf wrote:
You can use either the Worksheet_SelectionChange or Workbook_SheetSelectionChange events. Something like this:

Code:

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range) Sh.Cells.Interior.ColorIndex = xlNone If Target.Cells.Count = 1 Then With Rows(Target.Row).Interior .ColorIndex = 6 .Pattern = xlSolid End With End If End Sub

(If you use the Worksheet_SelectionChange event, just change sh.cells to cells).

Hope this helps,

rh

Click to expand...

Hi,

Two questions :

1.Is it possible that the color-row ended by the last used column and not to column 256

2.I have several colors in my sheet and do not want to delete them.
Many thanks for your time

Can somebody give a solution on this?
Thanks

Jack in the UK · May 21, 2002

Hi mate

i did not really like the code (no offence boys - you know me well enough to know im not having goes) that was close! I could see issues popping up soon enough.

see to me colour rows by condition is fine but needs tailoring, OK you want on cell change as the guys have shown this will be only dont by target method, and slow the sheet down a little as it runs the code each time the active cell changes.

but what is the far right cell, that is the last always last never to to further right cell, say a to z is always used AB is NEVER used z would be the column im looking for .

OK ill try to re write this, have a look at my feed, look at the bottom, i have taken care of this in conditional formating, as you ask here now.. that sort of thing.

http://www.mrexcel.com/board/viewtopic

NateO · May 21, 2002

Jack, your link is broken, am curious though. In the interim, I'll stick with a worksheet event, although as Russell mentions, and for multiple sheets, you'll want the Workbook_SheetSelectionChange procedure, which goes in the 'ThisWorkbook' module.<pre>
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Dim cl As Range
Static n As Range
On Error GoTo 1
For Each cl In Range("a" & n.Row, n)
If cl.Interior.ColorIndex = 36 Then _
cl.Interior.ColorIndex = xlNone
Next cl
For Each cl In Range("a" & Target.Row, Target)
If cl.Interior.ColorIndex = xlNone Then _
cl.Interior.ColorIndex = 36
Next cl
1: Set n = Target
End Sub</pre>

This is a worksheet event, it needs to go in a worksheet module. Right-click on the sheet, view code and paste the code.

It won't overwrite any color except index 36: "Mellow Yellow"

_________________
Cheers, NateO
This message was edited by NateO on 2002-05-21 15:39

Jack in the UK · May 21, 2002

Jack being simple try this: put the the required sheet

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Cells.Interior.ColorIndex = 0
With Target.EntireRow.Interior
.ColorIndex = 5
End With
End Sub

Yogi Anand · May 21, 2002

On 2002-05-21 11:48, dennisdjones wrote:
In current cell, would like to use a temporary color of the entire row that would travel up & down as I go to each cell (help to keep a better visual location of where at in the spreadsheet)

Hi dennisdjones:
I know you have plenty of information now on a VBA solution. I am proposing here a formula based CONDITIONAL FORMATTING that should meet your requirements.

Formula Is ... =and(cell("contents")<>"",row()=cell("row")) ... FORMAT|PATTERN ... color, say, green

Please keep in mind I have not tested it thoroughly -- but please report back how it behaves for you and let us take it from there.

Regards!

_________________
Yogi Anand

Edit: Deleted inactive website from hardcoded signature
This message was edited by Yogi Anand on 2003-01-19 13:03

Joe Was · May 21, 2002

In line with Jacks code, I use this as a highlighter. Select a cell once and it is colored, re-select it and it is un-colored. You can select as many cells as you want. To color a whole row just select the row header number! You must select out of the current selection then re-select the colored selection to turn off the highlight. The code works on the "View sheet code" page. Hope this also helps. JSW

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If Not Intersect(Target, Selection) Is Nothing Then
If Target.Interior.ColorIndex = 34 Then
Target.Interior.ColorIndex = 0
Else
Target.Interior.ColorIndex = 34
End If
End If
End Sub

CELL COLOR

dennisdjones

New Member

Excel Facts

NateO

Legend

Russell Hauf

MrExcel MVP

verluc

Well-known Member

verluc

Well-known Member

Jack in the UK

Well-known Member

NateO

Legend

Jack in the UK

Well-known Member

Yogi Anand

MrExcel MVP

Joe Was

MrExcel MVP

Similar threads

Forum statistics

Share this page

CELL COLOR

New Member

Excel Facts

Legend

MrExcel MVP

Well-known Member

Well-known Member

Well-known Member

Legend

Well-known Member

MrExcel MVP

MrExcel MVP

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock