Cell formatting based on criteria setup on another sheet

yes
in the conditional formatting formula
rather than use a formula

=A1 > 5

then use
=A1 > sheet2!$B$3
then the value added in sheet 2 cell B3 will be used

for a range
=AND ( A1 > sheet2!$B$3 , A1 < sheet2!$C$3 )

now the range is in
Sheet2 B3 and C3

Please upload a sample of your workbook to the forum, Make sure you have removed any private information, remember this is a public forum and so available to anyone
Would like to see an example of your data and also a manual mock up of the expected results you want to achieve.

To attach a file to your post,
click "Go advanced" (next to quick post),
scroll down until you see "manage Attachments",
click that and select "Choose File" (top Left corner).
Find your file, click "Open" click "upload" click 'close windows" Top Right. click "Submit Reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

Please upload a small clean sample of your data / workbook (not a picture) to the forum,

Originally Posted by JOHN H. DAVIS

Does this help?

Please Login or Register  to view this content.

Hmm, I guess I understand how you arrived at this as an answer, but it isn't what I'm looking for. Good solution if a "+" or "-" is involved, but in my case the +/- was to say a value of 4 to 6 is ok, but anything else should color the cell red.

As far as uploading my worksheet, I can't do that. The amount of stripping to eliminate secrets would take significant time for my level of understanding, I would probably end up in the penalty box as a result! LOL

It's ok if we don't come up with a solution on this one. It isn't a high priority. I will think of a better way to describe what I'm trying to do, but I suspect it might not be possible to conditionally format a cell on one sheet based on a value range on another sheet.

I often ask excel to do things it really doesn't want to/can't do. Nice thing is I have you guys to get me out of hot water and pointed in the right direction!

Originally Posted by PlankSpank

Is it possible for a user to enter values into a setup sheet to provide color formatting conditions for cells on another sheet.

Example:

I have a sheet that is currently setup so a cell will turn red if the value in that cell is above or below 5. Instead of formatting that cell, I'd like the user to be able to enter another value on another sheet, perhaps with a +/- value, to setup the conditional for cell color change.

Has anybody done this?

Yep, managed to confuse myself reading this again.

I intended it to convey this:

data sheet currently has a cell with conditional formatting that turns red if the value is less than 4 or greater than 6. It works great, no issues.

What I would like to do is:

use a setup sheet to set the error range on the data sheet cell as a user input. So in one cell the user would enter the low range, in this case 4. In another cell, the user would enter the high range, in this case 6. This would provide the ability for the user to enter a custom error range for the conditional format, which is turn the cell red if the value is outside the specified range.

Is that a more clear question? I'm trying here! LOL

but I suspect it might not be possible to conditionally format a cell on one sheet based on a value range on another sheet.

it is possible

heres an example

sheet 1 - put the range
row 5 is for between on sheet2
row 10 is for outside range on sheet3