Ask Your Question
1

conditional formatting of cell

asked 2018-01-03 18:23:27 +0200

mcfarlane gravatar image

I am trying to make a budget spreadsheet. I want the cell to change color to red if the entered currency is greater than a reference cell. This is what I have but nothing changes. I also need the standard currency format to apply.

[red][>b16]$##,###.##;[black]$##,###.##

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
0

answered 2018-01-09 01:12:52 +0200

librebel gravatar image

Hello @mcfarlane,

You could do this with “Conditional Formatting” as follows:

  1. Select the cell that you want to change the color of;
  2. Choose the menu “Format : Conditional Formatting : Condition...”;
  3. In the dialog that pops up, in the first row under Condition 1, enter the values: “Cell value is”, “greater than”, “B16”;
  4. In the listbox “Apply Style:”, enter the value “Bad”;
  5. Click on the “Add” button to add another condition;
  6. In the first row under Condition 2, enter the values: “Cell value is”, “less than or equal to”, “B16”;
  7. In the listbox “Apply Style:”, enter the value “Default”;
  8. Click OK.

Instead of the “Bad” style, you could also add your own style by selecting the item “New Style...” in the listbox “Apply Style:”.

HTH, lib

edit flag offensive delete link more

Comments

Thank you it worked. Is there a way to apply to several cells for same reference cell? Just to be more efficient. I tried highlighting and copying but would reference cell in front of current working cell.

mcfarlane gravatar imagemcfarlane ( 2018-01-14 03:03:39 +0200 )edit

You’re welcome @mcfarlane :),

To apply the conditional formatting to several cells at once, just select all the cells and proceed from step 2.

Alternatively you could specify the cell range address, below in the Conditional Formatting dialog in the textbox called Range: , e.g. for cells C1 to C5, you would enter C1:C5 there.

To accept the above answer as correct, please click on the checkmark icon on the left, and karma-permitting upvote it :D

librebel gravatar imagelibrebel ( 2018-01-14 03:41:10 +0200 )edit

I tried that, the first cell works but all the rest dont. Any thoughts? My first condition was to turn cell red if greater than reference cell. The rest of the cells turn red no matter what value is put in. My second condition wa's to default if less than or equal to reference cell as you ssid.

mcfarlane gravatar imagemcfarlane ( 2018-01-14 07:48:26 +0200 )edit

Please check if there are older (overlapping) formatting rules in the menu Format : Conditional Formatting : Manage..., and if so, delete them.

If the problem persists, then please upload a sample document ( after removing all sensitive data from it ), by editing your original question and clicking the paperclip button there.

librebel gravatar imagelibrebel ( 2018-01-14 16:40:39 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2018-01-03 18:23:27 +0200

Seen: 520 times

Last updated: Jan 09 '18