PDA

View Full Version : Conditional formatting



Aero
05-06-2010, 12:08 PM
I'm trying to use conditional formatting for cells, but i'm running into some problems..

This is what i'm trying to do:

- When nothing is entered in a cell, the cell should be red

http://i41.tinypic.com/akc1up.png

- When the value in the cell is 0, the cell should be green

http://i42.tinypic.com/f8geb.jpg

So far i have this as code (not working):


With ws.Range("A4")
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:="=ISBLANK(A4)"
.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="0"

.FormatConditions(1).Interior.ColorIndex = 3 ' red
.FormatConditions(2).Interior.ColorIndex = 43 ' green
End With

And ws is defined like this:
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets(1)
I can't see what i'm doing wrong.., the cell is red in the beginning, but when i enter 0 it doesn't turn green...

RonMcK3
05-06-2010, 01:35 PM
Aero,

It looks to me like you are very close. You need to use a Worksheet_SheetChange event; that way each time anything changes on the worksheet, the code is fired off and your field is updated if it was changed.

Try this:
Private Sub Worksheet_SheetChange()

Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets(1)

With ws.Range("A4")
Range("A4").Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=ISBLANK(A4)"
Selection.FormatConditions(1).Interior.ColorIndex = 3
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=A4=0"
Selection.FormatConditions(2).Interior.ColorIndex = 4
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=A4>0"
Selection.FormatConditions(3).Interior.ColorIndex = 34
End With


End Sub


In addition to your Red and Green conditions, I added a third setting A4 to Aqua whenever it's value is greater than zero.

Aero
05-06-2010, 01:42 PM
Hmm, that works, but i don't know why it does...
I'm familiar with ChangeEvents in Java but not in VBA
The only thing that looks different is the :

Range("A4").Select

Does that throw the SheetChange event ?
(i also tried adding a third setting, but as it didn't work with 2 settings yet, i excluded that one :), thanks )

mdmackillop
05-06-2010, 02:32 PM
Are you looking to CF one cell only, or should it apply to a range of cells?

Aero
05-06-2010, 02:56 PM
Are you looking to CF one cell only, or should it apply to a range of cells?

Right now i tried it for one cell,
but now i'm going to expand it to more cells :)

lynnnow
05-07-2010, 02:16 AM
Aero, if it is a particular column that needs to be CF'ed, then change

With ws.Range("A4")

to

With ws.Columns("A:A")

This will trigger off the change event if the cells in column A are changed.

HTH

Bob Phillips
05-07-2010, 03:02 AM
Hmm, that works, but i don't know why it does...
I'm familiar with ChangeEvents in Java but not in VBA
The only thing that looks different is the :

Range("A4").Select

That is done so that the CF is correctly applied. CF formulae adjust t the activecell, so Ron makes sure the requisite cell is active. He could have adjusted the formula according to the activecell, which is a better way IMO, but can be a lot more work.


Does that throw the SheetChange event ?

No it doesn't, changing the cell would, but not just selecting it.


(i also tried adding a third setting, but as it didn't work with 2 settings yet, i excluded that one :), thanks )

Show us what you did, you can have 3 conditions in pre-2007 Excel.

Aero
05-07-2010, 05:13 AM
Ok i understand it more now.

I wont have to apply it on a column but something like this:

http://i40.tinypic.com/16jmssi.png
I should be able to figure that out since i know it for one cell now.

The third condition i tried to add (in the beginning) was exactly the same as the one RonMcK3 added. However when asking my question i wanted to keep it simple so i only tried it with 2.

(It works correctly now btw)

Bob Phillips
05-07-2010, 05:16 AM
That should be okay, just use all of the cell addresses in the range statement.

Aero
05-07-2010, 06:41 AM
Hmm, i'm still having some problems with this, ..

I have 2 for loops to cover all the cells, but this doesn't seem to work:

(inside the for loops)
Dim cell As Variant
Set cell = ActiveSheet.Cells(j + 1, i)
cell.Select
cell.FormatConditions.Add Type:=xlExpression, Formula1:="=ISBLANK(cell)"
cell.FormatConditions(1).Interior.ColorIndex = 3
The 'cell' is correct because when i type:
cell.Interior.ColorIndex = 3 it fills the correct cells.

The problem i think lies in =ISBLANK(cell), when i enter an empty cell, i.e. ISBLANK(E3) than it works perfectly, all cells are red and when i enter something in E3, the backgroundcolors disappear. (like it should work)

So, ISBLANK takes a cell, but when i say: ISBLANK(cell) it doesn't work anymore..

EDIT: is that because i declared cell as a Variant ? (because CellFormat didn't work and Cells wasn't in the list)

Bob Phillips
05-07-2010, 07:09 AM
No exac tly, but cell is an object (with many properties), and formula expects a simple string.

Try



cell.FormatConditions.Add Type:=xlExpression, Formula1:="=ISBLANK(" & cell.Address(False,False) & ")"

Aero
05-07-2010, 07:17 AM
Hmm, that seems to work
I can see it's being converted to a string, but i don't understand what:
cell.Address(False,False) does ?

Is it something like this:
if cell = A3
cell.Address(False,False) returns A3 ?

Bob Phillips
05-07-2010, 07:22 AM
No, as I said before, cell is a range object with many properties (Value, Interior, Address, etc.).

cell.Address returns an absolute address of that cell, such as $A$3. By adding (False, False) one is saying that we want relative row, and relative column, i.e. A3.

So you are embedding the relative cell address into the formula string.

Aer0
05-09-2010, 02:20 AM
I'm trying to make a cell look green whenever it's value is : value of A5 + value of B5 (string-wise)

So if A5 is 4 and B5 is 0, and the cell is 40, the cell should turn green.

This is how i do this:

Dim uppercell As Range

Set uppercell = ActiveSheet.Range("C5")

With uppercell
.Select
Dim previousanswer As Range
Dim column As Range
Set previousanswer = ActiveSheet.Range("A5")
Set column = ActiveSheet.Range("B5")

.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:=" " & previousanswer.Value & column.Value & " "
.FormatConditions(2).Interior.ColorIndex = 4
End With
This works fine if the values in A5 and B5 are already filled in, before adding the formatcondition

However, i need the user to input the values into A5 and B5, and then it should check if the value in the cell is correct...

( EDIT: wanted to add some images but can't because you're not allowed to post links when you have a post count < 5 )

mdmackillop
05-09-2010, 03:20 AM
Aero,
You are only allowed one identity on the site. This thread will be merged with your existing one, and the Aer0 identity will banned.
You can continue to post with your Aero identity.
Regards
MD

Bob Phillips
05-09-2010, 03:33 AM
I'm trying to make a cell look green whenever it's value is : value of A5 + value of B5 (string-wise)

So if A5 is 4 and B5 is 0, and the cell is 40, the cell should turn green.

This is how i do this:

Dim uppercell As Range

Set uppercell = ActiveSheet.Range("C5")

With uppercell
.Select
Dim previousanswer As Range
Dim column As Range
Set previousanswer = ActiveSheet.Range("A5")
Set column = ActiveSheet.Range("B5")

.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:=" " & previousanswer.Value & column.Value & " "
.FormatConditions(2).Interior.ColorIndex = 4
End With
This works fine if the values in A5 and B5 are already filled in, before adding the formatcondition

However, i need the user to input the values into A5 and B5, and then it should check if the value in the cell is correct...

( EDIT: wanted to add some images but can't because you're not allowed to post links when you have a post count < 5 )

If a cell has CF, that will fire WHENEVER the value in that cell matches the condition, either initially, or when amended.

GTO
05-09-2010, 03:38 AM
Suddenly my simple-minded head hurts....

Okay, based only on post#14, I think the formatconditions might be off. Here is what I came up with:


Sub exa()
Dim rngCFEntry As Range

Set rngCFEntry = ActiveSheet.Range("C5")

With rngCFEntry
.FormatConditions.Add Type:=xlExpression, _
Formula1:="=CONCATENATE(" & .Offset(, -2).Address & "," & _
.Offset(, -1).Address & ")=""40"""
.FormatConditions(1).Interior.ColorIndex = 4
End With
End Sub

Maybe?

Mark

Aero
05-09-2010, 06:09 AM
Suddenly my simple-minded head hurts....

Okay, based only on post#14, I think the formatconditions might be off. Here is what I came up with:


Sub exa()
Dim rngCFEntry As Range

Set rngCFEntry = ActiveSheet.Range("C5")

With rngCFEntry
.FormatConditions.Add Type:=xlExpression, _
Formula1:="=CONCATENATE(" & .Offset(, -2).Address & "," & _
.Offset(, -1).Address & ")=""40"""
.FormatConditions(1).Interior.ColorIndex = 4
End With
End Sub
Maybe?

Mark
Yes, that seems to be a step into the right direction, the problem is that 40 is hardcoded now. However, i'm gonna run over several cells with a for loop which all have different values.
Basically it just has to check wether the value from the first cell and the value from the second cell concatenated together is the value of the current cell...

For instance, this works:
.FormatConditions.Add Type:=xlExpression, _
Formula1:="=CONCATENATE(" & previousanswer.Address & ";" & column.Address & ")=""40""" but this doesnt work:
.FormatConditions.Add Type:=xlExpression, _
Formula1:="=CONCATENATE(" & previousanswer.Address & ";" & column.Address & ")="" & uppercell.Value & """

Edit: (@mdmackillop (http://www.vbaexpress.com/forum/member.php?u=87), that was because i somehow locked myself out of this account. (changed email but didn't find activation code but it was in spam folder)

GTO
05-09-2010, 06:20 AM
...However, i'm gonna run over several cells with a for loop which all have different values.
Basically it just has to check wether the value from the first cell and the value from the second cell concatenated together is the value of the current cell...


I think it's imporant to note what Bob (xld) mentioned, in that once the CF is set, it will calculate and decide whther to mark the cell henceforth. Presuming that is what you want, what do you mean by the 'current cell'?

Are you looping through cells in Col C, and want to add the CF, or do you mean from whatever the active cell is, build the CF based upon the two cells left of the activecell?

GTO
05-09-2010, 06:35 AM
Off to hit the rack for this lad, maybe:


Sub exa3()
Dim rngCFEntries As Range
Dim rCell As Range

Set rngCFEntries = Range("C5:C10")
For Each rCell In rngCFEntries
With rCell
.FormatConditions.Add Type:=xlExpression, _
Formula1:="=VALUE(CONCATENATE(" & .Offset(, -2).Address & "," & _
.Offset(, -1).Address & "))=" & rCell.Address
.FormatConditions(1).Interior.ColorIndex = 4
End With
Next
End Sub

Aero
05-09-2010, 06:45 AM
Ok, i'll try to explain this as good as possible:

(note that all the red cells already have a conditional format, they become red when the cell is empty, the next image is the state in which it starts)
i'm going through the cells circled in blue:
http://i41.tinypic.com/2uynkvc.png
These are what i referred to as "current cells" or in the code fragment as: uppercell.
The value in " uppercell 1 " has to be this:
The value in the first encircled cell concatenated to the second encircled cell. ( = 40)
http://i40.tinypic.com/nffkhu.png

So if the user puts in 40 in that uppercell, it becomes like this:
http://i44.tinypic.com/9fr8kj.png

For the next "uppercells" it is the same principle, take the input from the cell that is one row to the left and a column up and concatenate it to the cell that is on top of the column.

What i'm now getting is:
http://i39.tinypic.com/zmfha9.png

(edit: testing your code now)

Aero
05-09-2010, 07:04 AM
Your code seems to work and do what i'm trying to do,

i'll try to adjust it now to my situation (as you can see in the images above).

Thanks so far for the huge support and if i'm continuing to experience problems, i'll post them. :)

Bob Phillips
05-09-2010, 07:04 AM
I have no idea where all of your data is, and this thread is fast losing my will to live, but a simple formula of

=C12=--(B11&C$7)

where C12 is the cell being tested, B11 is 1 row up, 1 column left, and C$7 is the anchored row, same column. Just adjust it to your data, your code.

Aero
05-09-2010, 07:12 AM
I have no idea where all of your data is, and this thread is fast losing my will to live, but a simple formula of

=C12=--(B11&C$7)

where C12 is the cell being tested, B11 is 1 row up, 1 column left, and C$7 is the anchored row, same column. Just adjust it to your data, your code.

It's just a macro for children to test long divisions (? google translate), so they have to input the data by typing the correct number in the cells.

I will try your suggestion also.

GTO
05-09-2010, 07:15 AM
... and this thread is fast losing my will to live, but a simple formula of ...

Of course a much better formula, but I had to log back on to say thanks for sending me to bed laughing. I damn near peed myself!

Bob Phillips
05-09-2010, 08:37 AM
I am French.I do not understand English, do not quite understand the contents of the article!

Which article?

mdmackillop
05-09-2010, 09:06 AM
Hi nopq682
You have nine posts, none of which contribute to the threads. You are welcome to browse, but please desist from these unneccessary comments.
Regards
MD

Aero
05-09-2010, 01:36 PM
EDIT: everything works now as it is supposed to be, it may be marked as solved

Bob Phillips
05-09-2010, 02:32 PM
What is the active cell? If it is say C7, then use



.FormatConditions.Add Type:=xlExpression, _
Operator:=xlEqual, _
Formula1:="=(B6/$C$1)*$C$1"


It may be due to my near comatose situation and thus I am missing something, but isn't



(40 \ Range("C1")) * Range("C1")


the same as just 40?

Aero
05-09-2010, 02:43 PM
What is the active cell? If it is say C7, then use



.FormatConditions.Add Type:=xlExpression, _
Operator:=xlEqual, _
Formula1:="=(B6/$C$1)*$C$1"

It may be due to my near comatose situation and thus I am missing something, but isn't



(40 \ Range("C1")) * Range("C1")

the same as just 40?

Let's assume the value of C1 is 16,

40 \ 16 gives me 2, multiplying it by 16 gives me 32..


I found a huge work-around for the problem and it works now,
i also edited my previous post.

Thanks for the help !