Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old July 20th 19, 09:26 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: May 2019
Posts: 37
Default 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



  #2   Report Post  
Old July 20th 19, 10:25 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,727
Default 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
  #3   Report Post  
Old July 20th 19, 11:04 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: May 2019
Posts: 37
Default 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
  #4   Report Post  
Old July 20th 19, 11:56 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,727
Default 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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Setting a cell's color based upon RGB values LordHog Excel Programming 6 October 23rd 10 04:25 AM
Add values based on text color with the criteria of a date Peter Gonzalez[_2_] Excel Worksheet Functions 7 April 15th 10 08:16 PM
Q: How to color a cell based on values in two cells abcd1234 Excel Programming 17 March 13th 06 02:03 PM
list two columnar values based on conditional formating (text color) beechum1 Excel Worksheet Functions 2 February 12th 06 09:53 AM
In excell be able to Sum values based on the color of the cell Ray Lewis Excel Worksheet Functions 0 October 5th 05 03:10 PM


All times are GMT +1. The time now is 01:13 PM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017