Conditional formatting based on formatting

[Guest]

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

 

[Guest]

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?

 

[Guest]

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

 

[Guest]

Think you will have to use the calculate event. I tried a UDF in conditional
formatting, and it caused excel to crash. Even if used through a defined
name.

Maybe someone else knows a better trick.

 

[Peter T]

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