Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > microsoft.public.excel.programming > #109427

Re: Colormarking of fields based on colors in another sheet (difficult !!)

Show all headers | View raw

Le 2016-10-10 à 07:20, jmslab@xs4all.nl a écrit :> Isabelle, Thanks a lot.
> You created it now as a ChangeWorksheet module. Can you please change it in a
> general one were I can push a button to run it. I will place it then under
> modules instead of direct under the datasheet. ps. I do not realy understand
> your question about the color table. I have to read that first in more
> detail, and then I will come back on that later.

voilà,

Private Sub test()

green = 6750054
red = 255
blue = 15986394
orange = 49407
cNone = 16777215

TargetRow = Selection.Row
rw = Application.Match(Range("I" & TargetRow), Sheets("Codes").Range("I:I"), 0)

'Step1: The existing filled colored in this sheet in column L and M and X till
CU, to change to 'no color'.
  If Selection.Column = 12 Then  'column "L"
   If Sheets("Data").Range("L" & TargetRow) <> "" Then
     Sheets("Codes").Range("L" & rw & ":CU" & rw).Copy
     Sheets("Data").Range("L" & TargetRow).PasteSpecial Paste:=xlPasteFormats
     Application.CutCopyMode = False
   Else
     Sheets("Data").Range("L" & TargetRow & ":CU" & TargetRow).Interior.color =
xlNone
     'clear the datas in this line (Mx:CUx), if date was cleared ?
   End If
   Selection.Select
End If

'Step2:

    For Each c In Sheets("Data").Range("M" & TargetRow & ":CU" & TargetRow)
      rw1 = Application.Match(Range("I" & TargetRow),
Sheets("Codes").Range("I:I"), 0)
      addr = Cells(rw1, Selection.Column).Address
      CodeColor = Sheets("Codes").Range(addr).Interior.color

     With Target.Interior
        Select Case CodeColor
           Case blue
              If Target <> 0 Then .color = green Else .color = CodeColor
           Case cNone
              If Target <> 0 Then .color = red Else .color = orange
           Case cNone
              If Target = 0 Then .color = CodeColor
        End Select
     End With
    Next

End Sub

isabelle

Back to microsoft.public.excel.programming | Previous | Next — Previous in thread | Next in thread | Find similar | Unroll thread

Thread

Colormarking of fields based on colors in another sheet (difficult !!) jmslab@xs4all.nl - 2016-10-04 09:01 -0700
  Re: Colormarking of fields based on colors in another sheet (difficult !!) isabelle <i@v.invalid> - 2016-10-07 21:11 -0400
    Re: Colormarking of fields based on colors in another sheet (difficult !!) jmslab@xs4all.nl - 2016-10-08 09:51 -0700
      Re: Colormarking of fields based on colors in another sheet (difficult !!) isabelle <i@v.invalid> - 2016-10-08 21:56 -0400
        Re: Colormarking of fields based on colors in another sheet (difficult !!) jmslab@xs4all.nl - 2016-10-08 22:51 -0700
          Re: Colormarking of fields based on colors in another sheet (difficult !!) isabelle <i@v.invalid> - 2016-10-09 23:23 -0400
            Re: Colormarking of fields based on colors in another sheet (difficult !!) isabelle <i@v.invalid> - 2016-10-10 03:57 -0400
              Re: Colormarking of fields based on colors in another sheet (difficult !!) isabelle <i@v.invalid> - 2016-10-10 04:24 -0400
                Re: Colormarking of fields based on colors in another sheet (difficult !!) jmslab@xs4all.nl - 2016-10-10 04:20 -0700
                Re: Colormarking of fields based on colors in another sheet (difficult !!) isabelle <i@v.invalid> - 2016-10-13 22:37 -0400
                Re: Colormarking of fields based on colors in another sheet (difficult !!) jmslab@xs4all.nl - 2016-10-13 20:40 -0700
                Re: Colormarking of fields based on colors in another sheet (difficult !!) isabelle <i@v.invalid> - 2016-10-14 09:01 -0400
  Re: Colormarking of fields based on colors in another sheet (difficult !!) xethubao 123 <xethubao 123@gmail.com> - 2016-10-10 02:38 +0100

csiph-web