Posts by jmcall10

Hi, I posted b4 to no avail. Maybe I wasnt clear hehe

Ok simple thing is:

I have this in the ThisWorkbook module:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target1 As Range)
     
      
    If Target1.Cells.Count > 1 Then Exit Sub
      
      
      
      If Not Intersect(Target1, Range("A6:AW34")) Is Nothing Then
        With Target1
            
            Select Case .Value
            Case "One": .Interior.ColorIndex = 38
            Case "Two": .Interior.ColorIndex = 18
            Case "Three": .Interior.ColorIndex = 35
            Case Else: .Interior.ColorIndex = xlNone
            End Select
        End With
    End If
     
     
     
     
End Sub

Now what I want is if the any cell in Column A is "(None)" then make it yellow
any cell in column B make it white

so some sort of case like:

Case "(None)": .Interior.ColorIndex = 36

Re: Hyperlink Formula

Thanks this works a treat

Thought I would mention the reason I am using this is I have a long list of links and if the location changes then I can simply use the find replace tool.

However if you make it a link using ctrl + k then you can't use the find replace tool.

Maybe someone will find this useful

Thanks again for solving the problem

Hi

I have the following formula

=HYPERLINK(("G:\Project\Test Project"),"Link"

Now this works fine, However I have a cell "A5" containing "Test Project"

And I would rather the link refernced the cell contents

So the code would look something like this;

=HYPERLINK(("G:\Project\"+A5+"),"Link")

or something to that effect, this obviously doesnt work hence why I am here.

Can anyone assist?

Thanks in advance

jmcall10

Re: Cell Format Dependant On Rows

hmm, I require the cells to be merged sorry.

If Cells A-H are merged for an entire column can you not just reference them with the A Column?

Cell Row Formatting

Hi, I posted b4 to no avail. Maybe I wasnt clear hehe

Ok simple thing is:

I have this in the ThisWorkbook module:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target1 As Range)
     
      
    If Target1.Cells.Count > 1 Then Exit Sub
      
      
      
      If Not Intersect(Target1, Range("A6:AW34")) Is Nothing Then
        With Target1
            
            Select Case .Value
            Case "One": .Interior.ColorIndex = 38
            Case "Two": .Interior.ColorIndex = 18
            Case "Three": .Interior.ColorIndex = 35
            Case Else: .Interior.ColorIndex = xlNone
            End Select
        End With
    End If
     
     
     
     
End Sub

Now what I want is if the any cell in Column A is "(None)" then make it yellow
any cell in column B make it white

so some sort of case like:

Case "(None)": .Interior.ColorIndex = 36

Re: Cell Format Dependant On Rows

Anyone?

Re: Cell Format Dependant On Rows

Not sure what your assumption is but I will try clarify.

Ok I have 7 columns. First column is A-H, second is I-P etc.

The cells are merged from A-H, I-P etc.

Here is a break down of the colours dependant upon the column

IF = "(None)"

A - H = Yellow
I - P = White
Q - X= White
Y - AF2 =White
AG2 - AN2 = White
AO2 - AV2 = White
AW2 - BD2 = Yellow

Hope this helps

Thanks in advance

jmcall10

Re: Cell Format Dependant On Rows

Sorry, I dont mean default as in a programming sense.

Basically I want certain cells to be yellow if "(None)" and some to be white.

Thats all.

jmcall10

Hi, thanks to all who helped on my last posts. This follows on from that.

Now I have

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
     
    If Target.Cells.Count > 1 Then Exit Sub
    If Not Intersect(Target, Range("A1:A10")) Is Nothing Then
        With Target
            Select Case .Value
            
            Case "(None)": .Interior.ColorIndex = Null
            Case "One": .Interior.ColorIndex = 38
            Case "Two": .Interior.ColorIndex = 18
            Case "Three": .Interior.ColorIndex = 35
            Case Else: .Interior.ColorIndex = xlNone
            End Select
        End With
    End If
     
End Sub

My problem is that columns A to H are yellow(36 I think) and are merged cells.
And Cells AW2 to BD2 are also yellow. The cells inbetween are white.

At the moment when the Cell value is "(None)" the cells turn white, I dont want this. I would like them to go to default.

Is there an If statement or something that would do this?

Thanks in advance

jmcall10

Re: More Than 3 Conditional Formatting

I have just seen it now, will look at it whilst at work.

Thanks again for all your help

jmcall10

Re: More Than 3 Conditional Formatting

Both Posts have been great!

Should be able to get it working ok.

Will try tomorrow. Good night for now

jmcall10

Re: More Than 3 Conditional Formatting

Thats great however i would like it so that I only have to post the code once in the file.

Is there a way that all sheets can use just the one code snippet?

Thanks again

jmcall10

Hi,

This is my first post so please be kind

The simple problem is I require more than the standard 3 conditional cell formatting that excel offers.

Basically I want the cells to be a certain colour dependant on what is in the cell.

The Cells will contain strings. For example if the cell contained the string "Apple" then the cell wud be read.

I want this for about 10 different strings and therefore 10 different colours.

I dont mind using icolor or whatever it is as I can convert that.

Then last thing I ask is that it be used through the workbook as I will have a few sheets that it needs to run on and dont want to have to put the code in for each individual sheet.

Any questions let me know.

Thanks in advance

jmcall10