Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Conditional formatting on other cell value Results

Hi,

I have a cell that is a weighted sum of other cells. I want to turn the sum cell's font red if any one of three certain summed cells are not present in the sum. I've done this for one row using the following very simple and crude code.

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim sum As Integer
    sum1 = Range("G10").Value
    
    sum2 = Range("H10").Value
    
    sum3 = Range("I10").Value
    
    sum = sum1 + sum2 + sum3
    
    Range("As10:AW10").Select
    
    If sum < 3 Then
        Selection.Font.ColorIndex = 3
    Else
        Selection.Font.ColorIndex = 1
    End If
    
End Sub
I want to repeat this for rows 11 through 250 or so. That is where I am stuck. I don't know how to set up an array and have excel loop through each array.

Any help is appreciated.

Hi,

I am trying to find a way to count the number of cells that are conditionally formatted with a certain fill colour.
I have searched the web and found several posts that say how tricky it can be and and some that suggest using a function that recreates the formula of the conditional formatting. but sadly, I do not know how to replicate easily the formula used, and the code i found to count the Conditional formats does not appear to work. I believe it is because of the type of conditional format I have selected.
So can any one help? here is what I am trying to do.

I have a quite large and complex spreadsheet that has lots of data, amongst this are 4 columns containing user ID's (A user can have a Main identifier and up to 3 alternate Identifiers). But I need to check for Duplicate user ID's across all 4 ID columns eg Does and ID exist in Col H, or Col K or Col M or Col T? if so colour all the duplicate cells in red.
Now Excel 2007's conditional formatting has let me do this very easily, because it has a built in function that allows me to format "only Unique or Duplicate Values" and it applies to the "Range selected". So I can select the non contiguous columns of H, K, M and T and select a fill colour of Red, and this works perfectly.

However, I need to count those coloured cells. As I said right at the beginning, I do not know how i could construct the formula to be the same as the conditional formatting.
The code I have found on other sites does not work on the cells Conditionally formatted in this way (its does work with cells that are conditionally formatted in a far simpler way, so I am sure it is not me using the code wrong)

Any help would be greatly appreciated

hi
I'm posting this as a separate thread because it is different than my previous question, although it will indirectly solve my previous problem!

I have a small problem.Can anybody please help me.

Please see the attachement to follow the problem.

Here for example in column D4,the entries are NST and STD

1)So i want it such that if first cell is NST, and 2nd cell is also NST and so on then the current cell(NST) which is same as the previous cell value
should be colored in WHITE using only Conditional Formatting and No macros.

So in the output(2nd screenshot) the 1st cell is NST and all other cells below that are in WHITE as it is same to its previous cell value hence they are in white.

2)Similarly I want it such that if first cell is STD, and 2nd cell is also STd and so on then the current cell(STd) which is same as the previous cell
should be colored in WHITE using only Conditional Formatting and No macros.

So in the output the 1st cell is STD and all other cells below that are in WHITE as it is same to its previous cell value hence they are in WHITE.

The Conditional Formatting for NST and STD cases should be dynamic,should be applied for whole(entire) column.

Thank You.

Hi,

I am trying to create a conditional formula to highlight cells with a 'W' in column M based on the name in column N (in the attached example I've used the name Bob).

So in other words, if there is a 'W' in column M and on the same row the name 'Bob' is in column N, then I would like the cell containing the 'W' to be filled.

I've tried a few times to make this happen but no luck so far.

Thanks in advance!

wace

Need some help with conditional formatting. I have one cell (B10) that will contain either "high", "medium", or "low". Based on one of these 3 values, I'd like to shade cell J15 a color with some white text. I got 1 of the 3 keywords working, not sure how to add in the other 2.

Any suggestions?

Hi guys,

Been hunting around the forums but cant seem to find quite what I want.

I want to specify the background of a cell to be red if the text in that cell contains a specific word.

e.g. cell a1 will have the text 'Rest of World' (without quotes). I want this to show as red if it contains the word 'of'.

Of course the cell may contain other words but this illustrates what I'm looking to achieve.

I tried the following conditional formula but this does not work:

=IF(SEARCH("of",H3)>1,1,0)

Thanks for feedback :-)

I have a document, that i want to highlight a cell, when its value (inputed by user) is greater than another cell value (value determined by formula) the highlight sometimes works, but not with any consistancy to be use able. attached is the document in question.
Sheet Name 'Body Fat' cell example if G3 is greater than H3, then highlight G3 Yellow. then use will know to continue filling out the document according to procedue. there will be other formats i'd like to do, but this one is the most important. i filled out example line with the first name on the list.
Any Help would be greatly appriciated, attched is also the data that will be imported. thanks again for the help, and feel free to critisize any other formulas or macros you desire. i'm certain they are all messy.

Hi there, I'm trying to create some conditional formatting rules for a grade sheet.
I currently have a setup worksheet which has a named range ("grades") which runs through a grading system (8a,8b,8c,7a,7b etc) and assigns each with a numeric value (1 through 22 since there are 22 grades)

Essentially, my conditional format needs to calculate the difference between 2 grades by looking up their numeric value and colouring based on differences I set.

I can successfully calculate the difference between 2 grades on my worksheet by using the following formula:

=(VLOOKUP(I4,grades,2,FALSE))-(VLOOKUP(F4,grades,2,FALSE))

This essentially looks up the numeric value for the 'Target Grade' (I4) and the numeric value for the 'actual grade' (F4) and calculates the difference between them (2 - since the grades are '4c' and '3b', they are within '2 grades' of each other)

When I apply a similar formula in my conditional formatting rules, I add an extra condition with another operator, for example...

=(VLOOKUP($I$3,grades,2,FALSE))-(VLOOKUP($F$3,grades,2,FALSE))>2

The formula gives an error if I remove the $ characters. In essence, the above formula should colour the target cell Red because the student has strayed too far away from their target grade...

I have applied an opposite rule to colour the cell green to check my logic (<=2 at the end) but the cells are not colouring.

My other uncertainty is how to refer to the 'current' cell in the conditional formatting statement. So, something like this...

=(VLOOKUP(CURRENTCELL,grades,2,FALSE))-(VLOOKUP(CURRENTCELL,grades,2,FALSE))>2

Any help would be greatly appreciated. I can script in VB if there is an easier way to do that, I can write in VB.NET but not really looked into excel before like this...

Hi, I'm trying to compare two cells - if the percentages in those cells
are within 2% of each other (+/-), then color the cells green. I'm
using conditional formatting but the formula won't work. Note: I have
to round these cells because the actual percentages are not exact -
instead of 15% they're actually, say .152 which appears as 15% in the
cell.

Can anyone tell me why this function doesn't work? In cell J23 the
value is .034, and cell I23 is .041 (Looks like 3% and 4% respectively
in the cells).

Conditional Formatting- Formula Is: =IF(ROUND(SUM(J23,1),2) =
ROUND(I23,2),1) then color the cell green.

Thanks!
Steve

My original question was posted a few minutes ago (scroll down to see) & I
got the answer below but it didn't work....I'm about to SCREAM!!!!

Can someone PLEASE help???
______________________________________________________________

Hi Jason,

I tried what you said but it is being stubborn & continuing to format the
wrong cells! Any other ideas I can try???

Much Appreciated!

_______________________________________________________

Using B$1 locks the row. Change it to B1.

HTH
Jason
Atlanta, GA

>-----Original Message-----
>Desparately in need of help!!!
>
>What I am trying to do is find the conditional
formatting that is going to
>highlight the "Not equal to" cells.
>
>In other words, below I have sample cell rows to the
left (1,2,3,...), a
>column "A" and then I have two sets of matching #'s, one
below another.
>
>I did a conditional formatting (from cell B2) that
said "Cell Values is"
>"Not equal to" "=B$1"...I set it to highlight/shade when
one of the two cells
>is not equal to the other.
>
>It works great for the 1st 2 rows...however when I do a
paint formatting
>from B2 down to the rest of the row, it formats the
wrong cells & every other
>cell highlights...In other words...B3 not equal to B2,
B5 not equal to
>B4....and so on!!!
>
>PLEASE...I'm desperate.....can someone help!!!
>
> A
>1 83543520
>2 83543520
>3 09341720
>4 109341720
>5 7503362001
>6 7503362001
>7 0501652001
>8 0501652001
>9 94485620
>10 94485620
>11 22122520
>12 22122520

Expand AllCollapse All

Desparately in need of help!!!

What I am trying to do is find the conditional formatting that is going to
highlight the "Not equal to" cells.

In other words, below I have sample cell rows to the left (1,2,3,...), a
column "A" and then I have two sets of matching #'s, one below another.

I did a conditional formatting (from cell B2) that said "Cell Values is"
"Not equal to" "=B$1"...I set it to highlight/shade when one of the two cells
is not equal to the other.

It works great for the 1st 2 rows...however when I do a paint formatting
from B2 down to the rest of the row, it formats the wrong cells & every other
cell highlights...In other words...B3 not equal to B2, B5 not equal to
B4....and so on!!!

PLEASE...I'm desperate.....can someone help!!!

A
1 83543520
2 83543520
3 09341720
4 109341720
5 7503362001
6 7503362001
7 0501652001
8 0501652001
9 94485620
10 94485620
11 22122520
12 22122520

I have been working on this but have not been able to figure it out so far. Basically I have numeric values set in rows and I need to compare the values to a certain cell in that row and then highlight the other cells in the row if they are greater than 10% of the numeric value of the reference cell.

So for instance: C4 contains the reference value and E4 to K4 need to be highlighted if they are 10% greater than the numeric value in C4.

Thank you,

Paul Hudgins

So my problem:

in one column i have values and they should be formatted based on the value in that cell but the top and down values of the rule should be based on value in other cell (categ1, categ2, categ3) and the category can be changed manually (cell value which decides the top and bottom values)

For example:

value in cell A1 is 900€ and in cell B1 is category1 (<500 red, >1000 green so so this should be yellow). BUT if i change manually the category in B1 to kat2 (where <1000 red...) the conditional formatting should now change the cell red because the category value in B1 changed.

Can this be done in excel2010 and how?

I'm using excel 2003 and wanted to know if it is possible to do conditional formatting based on the reference values of other cells. In the attached document, can I make columns Z, AA and AC shaded to the same color as AB?

My goal is to select this entire week of cells and just drag it down for a year.

Hello,

I am trying to write a formula for conditional formatting against a column of date values in a spreadsheet for tracking overdue and new items on a spreadsheet. I need a formula that will detect when it is Monday, and conditionally format everything that has been added new to the spreadsheet since Friday by highlighting it yellow.

I already have a formula which does this during other weekdays... (range is $F:$F, format conditions are highlight cell: yellow)

=TODAY()-F1<=1

And I was hoping to apply the same logic to capture Mondays as well. This is what I've tried:

=AND((WEEKDAY(NOW)=2),(TODAY()-F1<=3))

or

=(TODAY()-F1<=3)&(WEEKDAY(NOW)=2)

or

=(TODAY()-F1<=3)&(WEEKDAY(F1)=1,6,7)

None of these appear to have any effect whatsoever. It seems like this should be rather simple, and maybe I'm overthinking it, but I've been messing with this for days and haven't come up with anything. Help please?

Thanks a lot!

Hi,

I have an Excel-question, but can't find the answer anywhere on the internet. I'm really hoping you can help me with the following:

I have an Excel sheet with cells that are coloured red, yellow, or blue. Based on the specific colour of the cell, the value next to the coloured cell must have a certain value. For example, cell A1 is red. Because cell A1 is red, the content of cell B1 has to be 1. Cell A2 is green, and because of this colour the value in cell B2 has to be 2. Etc etc.

Conditional formatting (giving cells colours based on values) is relatively easy, but the other way aroud seems to be more difficult in Excel..

Thanks beforehand,
Timo (Netherlands)

Hi all, new here. Lurked a couple times but didn't register until now when I've really been stumped. Don't even know where to begin with this one.

Have a 4 worksheet 2007 excel file. Each worksheet has a table showing building status of all the rooms in a multistory building. It's a building status document. Basically worksheet 1,2,3 contain an individual room building status indicated by color. Worksheet 4 is an overall status tab. Colors are manually entered on worksheets 1 through 3. The 4th worksheet is supposed to change color of cell automatically based on if the cell is blue on all of the other worksheets. What I need to happen: If cell in worksheets 1,2,3 = blue, I need worksheet 4's corresponding cell = blue automatically.

Change worksheet example.jpg

complete worksheet example.jpg

Excel 2007. Tried to use Conditional formatting and honestly don't know if it has options to do this based off color not value and have no experience with it. I've tried looking into vb code but every example I find doesn't come close enough to this scenario for me to change it for my needs. (with my limited knowledge)

Any advice?

Hi all,

I am a novice with excel 2010 but need some help with conditional formatting and particularly copy & pasting formulas. Sorry about the per-amble below but it might just make it clearer what I am trying to do. So here goes:

I am trying to put together a training matrix for a construction site so that when I enter a date on a certain cell, another cell changes colour to suit specified date ranges. I doing this by subtracting one date from the other and converting it to a number. This is repeated numerous times for each This number is kept in a particular cell - call the cell P8. There is a range of rows with the identical formula. These other rows relate to other training that may of been taken. So there may be differntt value in, for example, P9, P10, P11, etc. This bit is complete.

Next I have Conditionally Formatted one cell - call the cell D8. The problem I have is when I try to copy & paste this cell to the next D9 it keeps the same formula rather than changing the formula to follow on or be a bit clearer,

cel($D$8) shows on the conditioanl formatting $P$8<1 then turn red. When I go to paste this into $D$9 the same formula appears i.e. in $D$9 $P$8<1 then turn red.

I want this instead to be in $D$9 $P$9<1 and the cell to turn red & so on, & so on.

There will be quite a lot of repetition like this & the only way, so far, I have been able to do this is by manual adjusting each cell.

Any help is greatly appreciated

Hi guys,

Thanks for a great forum.
I'm working on a spreadsheet in which I have a lot of "pairs" of rows like this:
1 (Target): ValueA ValueB ValueC etc etc
2 (Actual): ValueA ValueB ValueC etc etc

I want a conditional formatting of the "Actual" row cells based on the value of the cells above in the "Target" row. E.g.
If A$2 > A$1 --> Green
If A$2 = A$1 --> Yellow
If A$2 < A$1 --> Red

I use $ to lock the row, but obmit it for the columns.
PROBLEM: I want to copy the formatting to a LOT of cells while making sure the references change so that the formatting is always based on the cell directly above the cell in question. However, when I copy the formatting (e.g. with copy/paste special (formatting)) it keeps referencing back to the initial cell.
How do I code a conditional formatting so that I can just copy/paste the formatting to a lot of other cells while ensuring that the reference cell changes to match the cell directly above the cell with the formatting?
I'm sorry if it sounds confusing

Thank you in advance,

Christian

I consider myself a fairly strong Excel user, so I am at a loss on what is happening here. Basically, my conditional formatting works if the test expression is an absolute reference but fails if it is a relative reference. Here is my example.

1) Open up a new excel spreadsheet
2) Add these numbers in a row: 1 2 3 4 3 2 1 2 3 4 (that should be 10 cells with numbers in them)
3) Conditional format:
a) Cell Value equal to =min(your_cell_range)
b) Applies To =your_cell_range
c) Don't forget to make some format change (like making the text red)
4) As default, Excel will use absolute values for your_cell_range and it should work as expected by only highlighting the cells with a 1 in it.
5) Now, select your_cell_range, Manage Rules, and Edit the rule you just added. Change the absolute range in your min() equation to a relative range (that is, remove the $ signs in the range).
6) Apply this change and now the 1's are still highlighted, but so are the last 2, 3, & 4.

Changing the Applies To from relative to absolute does not seem to fix things, nor does toggling the 'Stop if True' checkbox.

This is a pain, since I want to use format painter to apply this formatting rule to other rows, but the absolute reference causes the painter to keep referencing the wrong row. With relative addressing, format painter works as expected, but I get this bug.

Can someone confirm they see this, too?
Any ideas?

Want to see something weirder? Go out about 6 cells beyond your last 4 (but still in the same row. Type in a 2. The formatting changes on your_cell_range, even though the cell that just had a 2 typed into it is not in the conditional formatting range.

Thanks,
Pauley


No luck finding an answer? You could always try Google.