Mark cell with a color based on 2 values setup in a text with a signin between
 

Hello,

If somebody like to solve a challange (and make me happy)

I've got a sheet with several columns and rows.
A system output shows were the data is changed with the shown combi of "old - new". If the data is changed from empty to something of from something to empty then it shows '-'

The format is always the same
For example;

200,000 - 210,7
'-' - 200
202,1 - '-'
150 - 140

What is the question... (challange);

I need to mark the cell;

red = if the value right = higher then the value left side (200,000 - 210,7).
green = if the value right = lower then the value left side (150 - 140).
bleu = if there was a value left side but now right it is '-' (202,1 - '-').
orange = if there was '-' left and now right side it is a value ('-' - 200)
no coloring if the cell is empty of left/right is identical.

This macro must run for the columns B till D and K till Z

Excel is allways a challange (and certainly the data somebody can prepare)


regard, Johan

Mark cell with a color based on 2 values setup in a text with a sign in between
 

Hi Johan,

Am Sat, 20 Jul 2019 01:26:02 -0700 (PDT) schrieb JS SL:

I've got a sheet with several columns and rows.
A system output shows were the data is changed with the shown combi of "old - new". If the data is changed from empty to something of from something to empty then it shows '-'

The format is always the same
For example;

200,000 - 210,7
'-' - 200
202,1 - '-'
150 - 140

What is the question... (challange);

I need to mark the cell;

red = if the value right = higher then the value left side (200,000 - 210,7).
green = if the value right = lower then the value left side (150 - 140).
bleu = if there was a value left side but now right it is '-' (202,1 - '-').
orange = if there was '-' left and now right side it is a value ('-' - 200)
no coloring if the cell is empty of left/right is identical.

that's easily done with conditional formatting.
Download the demo from he
https://1drv.ms/x/s!AqMiGBK2qniTgeVn...xjAEA?e=cTNkQz
and have a look for the rules of the conditional formatting.


Regards
Claus B.
--
Windows10
Office 2016

Mark cell with a color based on 2 values setup in a text with asign in between
 

Hi Claus,

The problem/challange is that the text/values are registered in the same cell.
So, for example;

A2= 200,000 - 210,7
B2= '-' - 200
C2= 202,1 - '-'
D2= 150 - 140

It are values in a cell as text and in between the 2 values a split -
The way you shows is normal single values in a cell. As you mentioned, yes you can solve this then easy with conditional formatting.
Excuus if I didn't explain that part clear enough

regards, Johan

Mark cell with a color based on 2 values setup in a text with a sign in between
 

Hi Johan,

Am Sat, 20 Jul 2019 03:04:10 -0700 (PDT) schrieb JS SL:

The problem/challange is that the text/values are registered in the same cell.
So, for example;

A2= 200,000 - 210,7
B2= '-' - 200
C2= 202,1 - '-'
D2= 150 - 140

It are values in a cell as text and in between the 2 values a split -
The way you shows is normal single values in a cell. As you mentioned, yes you can solve this then easy with conditional formatting.
Excuus if I didn't explain that part clear enough

sorry, I misunderstood the question.
Download the new file from here (macros are disabled in OneDrive):
https://1drv.ms/x/s!AqMiGBK2qniTgeVo...vk79w?e=dC5RV5


Regards
Claus B.
--
Windows10
Office 2016