Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
conditional formatting Copy colour of adjacent cell
I have various rows which are colour coded and I have added several columns
on to the end - One for a checker to initial and several others for approvals, comments and date. I want the checker Cell to change colour when initials are entered (e.g. =B10) I want the remaining cells on the row to change colour when approval initails are entered. I can do this with conditional formatting. The problem is I have been using Easyfilter to filter the colours - but when I try to copy formats it is picking up the hidden rows. I have resorted to copying and pasting the formatting only, but this is going to take days. Is there a way to conditional format the colour, to match the colour of another cell in the row? Regards Dylan Dawson Scotland |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
conditional formatting Copy colour of adjacent cell
I don't follow why you can't CF all cells in the row the same as your
checker cell. Otherwise describe more clearly what you've got and what formats you want to change under what condition(s). Regards, Peter T "DDawson" wrote in message ... I have various rows which are colour coded and I have added several columns on to the end - One for a checker to initial and several others for approvals, comments and date. I want the checker Cell to change colour when initials are entered (e.g. =B10) I want the remaining cells on the row to change colour when approval initails are entered. I can do this with conditional formatting. The problem is I have been using Easyfilter to filter the colours - but when I try to copy formats it is picking up the hidden rows. I have resorted to copying and pasting the formatting only, but this is going to take days. Is there a way to conditional format the colour, to match the colour of another cell in the row? Regards Dylan Dawson Scotland |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
conditional formatting Copy colour of adjacent cell
I Assume you mean the source cell that is used for the copy, when you say
checker cell. When I copy format down a column, it copies the colour from the checker cell down the entire row. When I use Easyfilter, and repeat this process, it copies the formatting to the filtered/ hidden rows. I want to set up a conditional format which will pick up the colour of the adjacent cell and use it, insteadof the colour of the checker cell. "Peter T" wrote: I don't follow why you can't CF all cells in the row the same as your checker cell. Otherwise describe more clearly what you've got and what formats you want to change under what condition(s). Regards, Peter T "DDawson" wrote in message ... I have various rows which are colour coded and I have added several columns on to the end - One for a checker to initial and several others for approvals, comments and date. I want the checker Cell to change colour when initials are entered (e.g. =B10) I want the remaining cells on the row to change colour when approval initails are entered. I can do this with conditional formatting. The problem is I have been using Easyfilter to filter the colours - but when I try to copy formats it is picking up the hidden rows. I have resorted to copying and pasting the formatting only, but this is going to take days. Is there a way to conditional format the colour, to match the colour of another cell in the row? Regards Dylan Dawson Scotland |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
conditional formatting Copy colour of adjacent cell
down the entire row.
do you mean across ? On the basis of slightly improved understanding a worksheet change event might be a better approach. When I suggested "describe more clearly what you've got" I meant example including (simplified) cell ref's of cells with format to be copied, to where and on the basis of what change in which cell(s) Regards, Peter T "DDawson" wrote in message ... I Assume you mean the source cell that is used for the copy, when you say checker cell. When I copy format down a column, it copies the colour from the checker cell down the entire row. When I use Easyfilter, and repeat this process, it copies the formatting to the filtered/ hidden rows. I want to set up a conditional format which will pick up the colour of the adjacent cell and use it, insteadof the colour of the checker cell. "Peter T" wrote: I don't follow why you can't CF all cells in the row the same as your checker cell. Otherwise describe more clearly what you've got and what formats you want to change under what condition(s). Regards, Peter T "DDawson" wrote in message ... I have various rows which are colour coded and I have added several columns on to the end - One for a checker to initial and several others for approvals, comments and date. I want the checker Cell to change colour when initials are entered (e.g. =B10) I want the remaining cells on the row to change colour when approval initails are entered. I can do this with conditional formatting. The problem is I have been using Easyfilter to filter the colours - but when I try to copy formats it is picking up the hidden rows. I have resorted to copying and pasting the formatting only, but this is going to take days. Is there a way to conditional format the colour, to match the colour of another cell in the row? Regards Dylan Dawson Scotland |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
conditional formatting Copy colour of adjacent cell
Peter,
Thanks for your advice and assistance, I think an event procedure would work. As a rough explanation: For the "Checked" Column (Column F) A change procedure whereby; If a cell in column F changes from being blank then it will also change colour to match the colour of the cell in column B of the corresponding row. For the "Approved" Column" (Column G) A change procedure whereby; If a cell in column G changes to "YES" then it will also change colour to match the colour of the cell in column B of the corresponding row. Does this make any sense? PS: "Peter T" wrote: down the entire row. do you mean across ? Yes across. Dylan D |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
conditional formatting Copy colour of adjacent cell
OK Dylan, think I follow now
Private Sub Worksheet_Change(ByVal Target As Range) Dim x As Long Dim cel As Range Dim rng As Range On Error GoTo errExit ' change "F1" to the first cell in col F to process Set rng = Intersect(Range(Range("F1"), _ Cells(Range("B65536").End(xlUp).Row, 7)), Target) If Not rng Is Nothing Then For Each cel In rng x = 0 With cel If .Column = 6 Then 'col F If .Value = "" Then x = xlNone Else x = .Offset(0, -4).Interior.ColorIndex 'col B End If ElseIf .Column = 7 Then 'col G If UCase(.Value) = "YES" Then x = .Offset(0, -5).Interior.ColorIndex 'col B Else: x = xlNone End If End If If x Then If .Interior.ColorIndex < x Then 'only set if necessary to minimize loss of Undo .Interior.ColorIndex = x End If End If End With Next End If errExit: End Sub Although you didn't ask this also removes colour if say Col-F cell is deleted or Col-G cell is changed from 'Yes". Regards, Peter T "DDawson" wrote in message ... Peter, Thanks for your advice and assistance, I think an event procedure would work. As a rough explanation: For the "Checked" Column (Column F) A change procedure whereby; If a cell in column F changes from being blank then it will also change colour to match the colour of the cell in column B of the corresponding row. For the "Approved" Column" (Column G) A change procedure whereby; If a cell in column G changes to "YES" then it will also change colour to match the colour of the cell in column B of the corresponding row. Does this make any sense? PS: "Peter T" wrote: down the entire row. do you mean across ? Yes across. Dylan D |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
conditional formatting Copy colour of adjacent cell
Peter,
It works perfectly thanks. One more thing (as Columbo would say) How do I make columns 8-13 change colour when "YES" is added to Column 7? Much appreciated Dylan D "Peter T" wrote: OK Dylan, think I follow now Private Sub Worksheet_Change(ByVal Target As Range) Dim x As Long Dim cel As Range Dim rng As Range On Error GoTo errExit ' change "F1" to the first cell in col F to process Set rng = Intersect(Range(Range("F1"), _ Cells(Range("B65536").End(xlUp).Row, 7)), Target) If Not rng Is Nothing Then For Each cel In rng x = 0 With cel If .Column = 6 Then 'col F If .Value = "" Then x = xlNone Else x = .Offset(0, -4).Interior.ColorIndex 'col B End If ElseIf .Column = 7 Then 'col G If UCase(.Value) = "YES" Then x = .Offset(0, -5).Interior.ColorIndex 'col B Else: x = xlNone End If End If If x Then If .Interior.ColorIndex < x Then 'only set if necessary to minimize loss of Undo .Interior.ColorIndex = x End If End If End With Next End If errExit: End Sub Although you didn't ask this also removes colour if say Col-F cell is deleted or Col-G cell is changed from 'Yes". Regards, Peter T "DDawson" wrote in message ... Peter, Thanks for your advice and assistance, I think an event procedure would work. As a rough explanation: For the "Checked" Column (Column F) A change procedure whereby; If a cell in column F changes from being blank then it will also change colour to match the colour of the cell in column B of the corresponding row. For the "Approved" Column" (Column G) A change procedure whereby; If a cell in column G changes to "YES" then it will also change colour to match the colour of the cell in column B of the corresponding row. Does this make any sense? PS: "Peter T" wrote: down the entire row. do you mean across ? Yes across. Dylan D |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
conditional formatting Copy colour of adjacent cell
How do I make columns 8-13 change colour when "YES" is added to Column 7?
so including Col-G as before that's 7 columns 7-13 Private Sub Worksheet_Change(ByVal Target As Range) Dim x As Long, v As Variant Dim cel As Range Dim rng As Range On Error GoTo errExit Set rng = Intersect(Range(Range("F1"), _ Cells(Range("B65536").End(xlUp).Row, 7)), Target) If Not rng Is Nothing Then For Each cel In rng x = 0 With cel If .Column = 6 Then 'col F If .Value = "" Then x = xlNone Else x = .Offset(0, -4).Interior.ColorIndex 'col B End If If .Interior.ColorIndex < x Then .Interior.ColorIndex = x End If ElseIf .Column = 7 Then 'col G If UCase(.Value) = "YES" Then x = .Offset(0, -5).Interior.ColorIndex 'col B Else: x = xlNone End If With .Resize(1, 7) v = .Interior.ColorIndex If IsNull(v) Then v = -1 If v < x Then .Interior.ColorIndex = x End If End With End If If x Then End If End With Next End If errExit: End Sub Regards, Peter T "DDawson" wrote in message ... Peter, It works perfectly thanks. One more thing (as Columbo would say) How do I make columns 8-13 change colour when "YES" is added to Column 7? Much appreciated Dylan D "Peter T" wrote: OK Dylan, think I follow now Private Sub Worksheet_Change(ByVal Target As Range) Dim x As Long Dim cel As Range Dim rng As Range On Error GoTo errExit ' change "F1" to the first cell in col F to process Set rng = Intersect(Range(Range("F1"), _ Cells(Range("B65536").End(xlUp).Row, 7)), Target) If Not rng Is Nothing Then For Each cel In rng x = 0 With cel If .Column = 6 Then 'col F If .Value = "" Then x = xlNone Else x = .Offset(0, -4).Interior.ColorIndex 'col B End If ElseIf .Column = 7 Then 'col G If UCase(.Value) = "YES" Then x = .Offset(0, -5).Interior.ColorIndex 'col B Else: x = xlNone End If End If If x Then If .Interior.ColorIndex < x Then 'only set if necessary to minimize loss of Undo .Interior.ColorIndex = x End If End If End With Next End If errExit: End Sub Although you didn't ask this also removes colour if say Col-F cell is deleted or Col-G cell is changed from 'Yes". Regards, Peter T "DDawson" wrote in message ... Peter, Thanks for your advice and assistance, I think an event procedure would work. As a rough explanation: For the "Checked" Column (Column F) A change procedure whereby; If a cell in column F changes from being blank then it will also change colour to match the colour of the cell in column B of the corresponding row. For the "Approved" Column" (Column G) A change procedure whereby; If a cell in column G changes to "YES" then it will also change colour to match the colour of the cell in column B of the corresponding row. Does this make any sense? PS: "Peter T" wrote: down the entire row. do you mean across ? Yes across. Dylan D |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
conditional formatting Copy colour of adjacent cell
Peter
Thank you for helping me with this. Regards Dylan |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional formatting based on adjacent cell? | Excel Worksheet Functions | |||
Conditional Formatting-Referencing adjacent cell | Excel Discussion (Misc queries) | |||
Conditional Formatting Based on Value of Adjacent Cell | Excel Discussion (Misc queries) | |||
Conditional Formatting based on adjacent cell value | Excel Discussion (Misc queries) | |||
How to do a conditional formatting based on an adjacent cell | Excel Discussion (Misc queries) |