Typically user selects cell(s) before formatting. You could try the
following which should format cell to right of any in "myRange" that's
bright green after user makes a new selection.
Dim muffling As Boolean
Dim miring As Range
Private Sub Worksheet_Deactivate()
If muffling Then
Colour
End If
End Sub
Private Sub Worksheet_Selection(By Target As Range)
If muffling Then
Colour
End If
If Not Intersect(Range("myRange"), Target) Is Nothing Then
muffling = True
Set miring = Intersect(Range("myRange"), Target)
End If
End Sub
Private Sub Colour()
Dim cel As Range
On Error GoTo errH
' #4 is the colorindex for Bright Green in a default palette
For Each cel In miring
If cel.Interior.ColorIndex = 4 Then
If cel.Column < Me.Columns.Count Then
cel.Offset(0, 1).Interior.ColorIndex = 4
End If
End If
Next
errH:
muffling = False
Set miring = Nothing
End Sub
Code belongs in the relevant sheet module. Right-click sheet tab > View Code
In this example "myRange" is a named range, only refer to this name as above
in a sheet module containing the name. Note named ranges can be moved, eg
drag cells, insert columns etc or removed. Alternatively hard code the
address of cell(s) to check.
As written the code does not clear the 'right' cell colour if left cell's
green is changed, easily adapted.
Regards,
Peter T
You could try the following and see if
"TheRook" <(E-Mail Removed)> wrote in message
news:F2679BC9-6D89-48DA-A43B-(E-Mail Removed)...
> Yes, I think so. The cell will be filled in by someone so the same logic
can
> not be adapted.
>
> I am not experienced in VBA but have previously posted regarding counting
> coloured cell and thought there maybe a simular way to do this.
>
> Cheers
>
> "Tom Ogilvy" wrote:
>
> > If B1 is filled with bright green by conditional formatting, then you
would
> > use similar logic for the other cell. Instead of Cell Value is, you
might
> > need to change the dropdown to formula is in the Conditional formatting
menu.
> >
> >
> > If the cell is colored by the user, then there would be no easy way to
> > detect that since coloring a cell does not trigger an event or a
recalculate
> > and there are no built in functions that will detect the coloring of a
cell.
> > You could write a user defined function in VBA possibly, is that what
you
> > want?
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> > "TheRook" wrote:
> >
> > > I am wanting to conditionally format a cell so that it fills the cell
in
> > > bright green if a specific cell is formatted bright green. Example
> > >
> > > If A1 is filled bright green, B1 would automatically fill in bright
green.
> > >
> > > Is this possible
|
Similar Threads
