formula and Conditional Format

[sprucethebruce]

Could I get a cell with a name in it to go a certain colour when another cell has 'TRUE', written in it?

I'm sure this is simple but I can't for the life of me figure it out.

 

[saltkev]

Hi Give This a Go

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Range("E4").Text = "TRUE" Then

Range("B4").Interior.Color = 65535
    
Else

 Range("B4").Interior.ThemeColor = xlThemeColorDark1    
 
 

End If
 
End Sub

Each time a selection changes, if the true condition is met in Cell E4 then the Interior colour of cell B4 is yellow.

Cheers


Kev

 

[GlennUK]

Using Conditional Formatting instead of VBA, select the cell to be coloured, and just use a Formula Is condition, of:

=$A$99

... or whatever the cell reference is that you want to test. And choose the colour fill that you want when that cell is TRUE.

 

[sprucethebruce]

Using Conditional Formatting instead of VBA, select the cell to be coloured, and just use a Formula Is condition, of:

=$A$99

... or whatever the cell reference is that you want to test. And choose the colour fill that you want when that cell is TRUE.

Excellent, thank you.

 

[MyDogJake]

Using Conditional Formatting instead of VBA, select the cell to be coloured, and just use a Formula Is condition, of:

=$A$99

... or whatever the cell reference is that you want to test. And choose the colour fill that you want when that cell is TRUE.

What if the cell I want highlighted could have a mixed content ... like ("Apr", "Apr/May", or even "15-Apr") ... in this case I'd want anything with the word "Apr" to highlight

Thank you for any / all help!!

 

[GlennUK]

What if the cell I want highlighted could have a mixed content ... like ("Apr", "Apr/May", or even "15-Apr") ... in this case I'd want anything with the word "Apr" to highlight

Thank you for any / all help!!

How about something like:

=NOT(ISERROR(FIND("APR",UPPER(C1&TEXT(C1,"d/mmm/yy")))))

changing the C1's to whatever cell reference you need.

 

[GlennUK]

What if the cell I want highlighted could have a mixed content ... like ("Apr", "Apr/May", or even "15-Apr") ... in this case I'd want anything with the word "Apr" to highlight

Thank you for any / all help!!

How about something like:

=NOT(ISERROR(FIND("APR",UPPER(C1&TEXT(C1,"d/mmm/yy")))))

changing the C1's to whatever cell reference you need.

 

[MyDogJake]

Thanks Glen,

How would I rephrase this formula so that the "Apr" portion converts to whatever is placed into my reference cell...(a drop down field I'm building)

Ex: I type in "Sep/Oct" into the search field (my reference cell) ... my goal is to highlight all instances within my selected range (in this example "Sep") ...

 

[GlennUK]

Thanks Glen,

How would I rephrase this formula so that the "Apr" portion converts to whatever is placed into my reference cell...(a drop down field I'm building)

Ex: I type in "Sep/Oct" into the search field (my reference cell) ... my goal is to highlight all instances within my selected range (in this example "Sep") ...

You'll have to explain that ... if you are typing "Sep/Oct" how does that match "Sep"? It doesn't, in my mind ... even if I know what you are aiming for, it still is not a match. How many cases like this would you be using? Maybe Jan/Feb/Mar or Aug-Dec even? If you are trying to highlight 2 months then use 2 conditional formats, one for each month, is one way of doing it.

 

[MyDogJake]

Thanks Glenn,

Sorry for the confusion... I suppose I may be asking the wrong question or using the wrong feature to do what I want to do with my calendar.

I used a data validation list to compile options for a drop down (in this case, months Jan-Dec) ... I then pushed out my calendar to our team for updating.. what I got back was folks typing in answers like 15-Apr, Apr/May or simply Apr.

I thought I could create a conditional format that would highlight the month in my drop down choice (like Apr) but I noted that only "Apr" would highlight. Perhaps I'm making this too hard? I'm green with Excel functions thus my struggle.

I suppose I could limit the choices for entry?

Does this help?
Do you have another idea?