I have played with this. Picking out the cells with direct references rather than intersecting rows and columns would probably make the solution less obscure. Mind you, the problem is somewhat obscure itself!
The objective is to develop formulae Is_K? and Is_Y? that can be evaluated within each cell of your grid and determine whether a conditional format is active or not (in the attached, I have evaluated Is_K? in the cell as well as evaluating it as a conditional format criterion).
A key element of the formula is = ( MEDIAN( K Offset.range, offset ) = offset )
I have used two names to identify the (vertical) bounds on the quantity with reference name 'K'. One name is the row range 'K' and the other identifies the four values in the 'Offset' columns. The two ranges separated by a space is the intersection of the row and columns and evaluates to give =$R$5:$S$5
The name 'offset' is a relative reference that looks up the current row label on the 'vertical' axis.
The MEDIAN function returns the middle value of a set of values. When there are three values comprising upper and lower bounds and a test value, the test value will be returned only if it is within the bounds. This gives a single test that replaces separate checks against the upper and lower bounds individually.
Multiplying the above formula by
* ( MEDIAN( K Chainage.range, Chainage ) = Chainage )
causes a 1 to be returned only if both the row and column are within range.
I may have misunderstood your requirement. The conditional formats I provided allowed the user to define the areas of blue and yellow. What it did not attempt was to take a pre-defined area and then change its reference colour.
To achieve that, I redefined the fixed rows 'Y' and 'K' to be determined by looking up a colour with MATCH and returning a dynamic row with INDEX.
The rest of the calculation goes through as before.
The (exclusive) use of names is a foreign concept as far as the bulk of the spreadsheet community is concerned but here I have been able to modify the definition of two ranges and in doing so change 8 cell references. The changes then propagate through without further modification.
Thanks for your help so far. But my concern is what if I want to use this technique in another excel workbook and in a larger area with more colour references. How will I be able to do this? The formula you made and all other things, I just want those steps badly sir. But believe me you are awesome. Btw how will I perform this exercise in another excel workbook in a larger area and with more color references? If you can guide me step by step then it will be very helpful for me. Even a video will also work if you can otherwise as you wish sir.
The key point is that I do not believe it possible to define the formula for a conditional format and then change the format itself by formula.
The process has to be the other way around. You define a separate conditional format for each colour. A formula is used to return TRUE/FALSE for each cell in the region but that formula needs to contain MATCH (or possibly VLOOKUP) to look for the first instance of the colour in order to select the actual data to be used from your table.
You can test one of the conditional formatting formulas within the conditionally formatted range, but to develop the next, you would need to assign the formula to a Name (e.g. 'is_Blue?') and use the Name to make the Conditional Formatting evaluate the formula.
The attached file shows how an additional condition may be added. It involves a new conditional format with the formula 'is_Orange?' and a range 'Orange' that is used in the formula.
I suspect the way in which I use names makes the workbook difficult for you to follow. If anyone else thinks they can express this solution, or another, using a more conventional approach please feel free to chip in.