conditional formating?? 2

[Chrissirhc]

Hello

I have one cell and when it has the word "this" in it, I can use conditional formating to make the cell green. What I would like to do is make the whole row green. How do I do this?

Thanks in advance

 

[Sypher2]

I don't think there's a way to do that with conditional formatting, although I could be wrong. All I can think of is to write a simple macro.

Sub CheckCell()
Range("A1").Select
If Selection.Text = "this" Then
    Selection.EntireRow.Select
    Selection.Interior.Color = vbGreen
End If
End Sub

Replace "A1" with whatever cell you are evaluating. Not sure if this will work for you, since you'd have to run the macro to change the color. It wouldn't happen automatically.

 

[Chrissirhc]

It doesn't seem to work even when I run the macro.
I've gone to tools-->macro-->visual basic editor.

In the editor I have chosen "view code" and then copy pasted your code changed the cell reference. Went back to excel played the macro and nothing happened.

 

[Sypher2]

Insert a standard module and put the code in that.

 

[Chrissirhc]

Nice on cheers

It works even though I don't know what a module is. Okay can I put a loop so that I can go down each cell and do a series of checks (I.e a few different if statements)

Sub CheckCell()

for (The the range I want to loop through)
Range("F(INCREMENT)&quot.Select
If Selection.Text = "No reply" Then
Selection.EntireRow.Select
Selection.Interior.Color = vbGreen
End If
If Selection.Text = "" Then
Selection.EntireRow.Select
Selection.Interior.Color = vbRed
End If

End Sub

Something like the above I take is possible?

 

[Sypher2]

Are you going down one column? If so, try this:

Sub CheckCell()
For i = 1 to x '(Change x with number of cells)
     Range("A" & i).Select '(Change A with your column)
     If Selection.Text = "No Reply" Then
          Selection.EntireRow.Select
          Selection.Interior.Color = vbGreen
     ElseIf Selection.Text = "" Then
          Selection.EntireRow.Select
          Selection.Interior.Color = vbRed
     Else
          Selection.EntireRow.Select
          Selection.Interior.Color = vbWhite
     End If
Next
End Sub

I am assuming there may be other text in these cells besides "No Reply" and blank. True? You will want to have something that changes it back to white if that text gets changed.

By the way, a module is just the normal place macro code goes. If you record a macro, Excel will automatically create a module and put the code in there. If you write your own, you have to insert a module. Fairly simple.

 

[Chrissirhc]

Hello

 

[Chrissirhc]

Sorry, I just looked and I tried to use the like function
ElseIf Selection.Text Like "Interview*" Then
blah blah

Incidently where do I get a colour list (I want to use purple etc) and is there a way to run a macro every time you update a field in a the sheet?

 

[GeekGirlau]

Nice one Sypher2 B-)

Just a couple of suggestions:

- If you name the range, you don't have to change the macro at all - it will loop through all the cells within that named range. I've used the name "FormatRow" in the sample below.

- Use SELECT CASE rather than IF when checking for a few different values.

- I've changed the SELECT CASE in the sample below to look at the lower case text typed in the cell - this will cope with "NO REPLY", "No reply", "No Reply", "no reply" and any other permutation you can think of!

- I've also set the formatting for any other option to no interior colour rather than white.

Sub CheckCell()
Dim objCell As Object


For Each objCell In Range("FormatRow&quot
Select Case LCase(objCell.Formula)
Case "no reply"
objCell.EntireRow.Interior.Color = vbGreen

Case ""
objCell.EntireRow.Interior.Color = vbRed

Case Else
objCell.EntireRow.Interior.ColorIndex = xlNone
End Select
Next
End Sub

 

[Sypher2]

Very elegant code there, GeekGirlau. I never have gotten in the habit of using Select Case structures.

Chrissirhc, I don't know if there's a way to run the macro when a cell is updated. I'd imagine there probably is, but I don't know how to do it. I've never personally had a need for on the fly cell coloring, so have never researched it. Doesn't mean it can't be done, though.

As for a listing of colors. . . The "vb" type colors are limited. This is straight out of the object library:

vbBlack, vbBlue, vbCyan, vbGreen, vbMagenta, vbRed, vbWhite, vbYellow

However, you can also use RGB numbers to get pretty much any color you want. i.e.

  Selection.Interior.Color = RGB(0,0,0)

The numbers go from 0 thru 255 (zero being no shading of that color, 255 being full). 1st one is red, 2nd green, 3rd blue.

 

[GeekGirlau]

To run a macro when the cell is updated, you need to move your code to the SheetChange event for your workbook.

Open your workbook and press [Alt-F11]. Make sure you can see the Project Explorer on the left hand side (the top-left window should be titled "Project - VBA Project". If you can't see it select View, Project Explorer.) You need to double-click on the "ThisWorkbook" module for your workbook. If your spreadsheet is called "MyData.xls", the Project Explorer will show the following:

VBAProject (MyData.xls)
Microsoft Excel Objects
Sheet1
Sheet2
Sheet3
ThisWorkbook

Once you've double-clicked on ThisWorkbook, at the top of the screen you'll see a drop-down box that says "(General)" - change this to "Workbook". Then change the next drop-down box to "SheetChange". Paste your macro in here - everything except the "Sub" line at the top and the "End Sub" at the bottom.

After saving your workbook, every time you make a change the macro will run.

 

[GeekGirlau]

Another quick tip Chrissirhc:

If you want a "Like" function, instead of using

Select Case LCase(objCell.Formula)
use
Select Case Left(LCase(objCell.Formula),9)

This gives you the first 9 characters in the cell. Each "case" that you use only needs to list 9 characters.

If you need to find text in the middle of the cell, you'll need to use the Instr function, which finds one phrase in the middle of another. To find "interview", you would use the following test:

If Instr(1,lcase(objCell.Formula),"interview&quot>0 then

The only limitation here is that you won't be able to use the Select Case - you'll have to go back to If and ElseIf.

 

[Chrissirhc]

Thanks geekgirlau,

I've looked at the code don't quite get whats going on. The only problem is if there is nothing in the cell I want to look at another cell on the row and check whats in there. Can't I nest an If in the CASE statements.

One problem with this new code is that there isn't a counter so I can't just select "C" % i //Where i is the counter.

Can I get the row we are looking at and can I nest if in a case statement?

Thanks

Chris

 

[Chrissirhc]

Case ""
Range("C" & objCell.GetRow).Select
If Selection.Text = "this" Then
Selection.EntireRow.Select
Selection.Interior.Color = vbBlack
End If

Thats what I tried

 

[Chrissirhc]

Case ""
Range("C" & objCell.GetRow).Select
If Selection.Text = "this" Then
Selection.EntireRow.Select
Selection.Interior.Color = vbBlack
End If

Thats what I tried and I'm getting error on the Range line.

 

[GeekGirlau]

Try
Range("C" & objCell.Row).Select

 

[epruittjr]

Your original problem can be solved with conditional formatting. If the cell you are checking is B2 then use the following conditional formatting:

In cell B2: Use the "Cell Value Is" and set the condition to ="This".

In all the other cells in row 2 that you need: Use the "Formula Is" and set the condition to =B2="This".

Anytime the cell B2 is equal to "This" all the cells in row 2 that you have formatted will display the formatting that you set.

 

[epruittjr]

The value in the second part should have read:

=$B$2="This".

That way you can use the copy and then the paste special functions to copy the formatting to all the other cells in row 2 that you need.