|It might help if you provided just a few more details about your spreadsheet and how it works, but I'll toss this out as a possible solution.|
You could loop through the range until you find the cell that meets one if the conditions you used in your CF.
For example, let's say your CF conditions are:
If cell = 1, CF = Red
If cell = 2, CF = Yellow
If cell = 3, CF = Green
Since you said that only one cell will be colored at any given time, then only one cell could contain 1, 2 or 3 at any given time.
This code will check each cell in A1:Z1 and as soon as it finds a 1, 2 or 3 it will fill A1 accordingly.
Note: This code is not concerned with your conditional formatting. It is simply looking for the same values that caused your Conditional Formats to be applied. Even if you removed or changed all of your CF conditions, this code would still fill A1 based on it finding a 1, 2 or 3.
If you need to determine which CF condition is applied to a given cell, things get much more complicated.
To use the code offered below,
- Right click the sheet tab for the sheet you are working with
- Choose view code and paste this code in the pane that opens.
- Modify the Case statements to match your actual conditions, or tell us what your exact conditions are, and we'll suggest the changes required.
The code will fire whenever you make a change to the sheet.
Private Sub Worksheet_Change(ByVal Target As Range)
'Loop through range B1:Z1
For myCol = 2 To 26
'If a 1, 2 or 3 is found, set A1 Fill Color
Select Case Cells(1, myCol)
'1 = Red
Range("A1").Interior.ColorIndex = 3
'2 = Yellow
Range("A1").Interior.ColorIndex = 6
'3 = Green
Range("A1").Interior.ColorIndex = 4
'1, 2 or 3 not found? Clear A1
Range("A1").Interior.ColorIndex = -4142