YouTubersLoveExcel#43: Data Validation & Conditional Format

Hi, how do i set conditional format to a List that is validated. So for example if 1-4 is a list in validation, how do i set the colors for each number, so when i choose a number the color would change as too.


Sheet2

  A B 1 1 Orange 2 2 Brown 3 3 Purple 4 4 Red

Excel tables to the web >> Excel Jeanie HTML 4

Hello Members, Sorry I'm reposting this To see if I can get a definitive anwser.
My Dilmela is: Choosing a value thru data a valadation list that populates cells in the worksheet.

If cell I5 contains the value 6H I would want the fonts weather the cells are numbers or text to be invisable in cells G12 thru Q30.

Also, If cell I5 contains a value of 4g6g cells A12 thru H30 to be invisable.

If cell I5 contains any other values the cells will be visable.

I copied the code below and it works only if data is input into cell I5 manually.

But, cell I5 is populated thru a formula:=VLOOKUP(I4,'THREAD CHART'!A3:Q937,17,FALSE)

Is there any code or guidence that I can accomplish this task???


Code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim d As Range, e As Range
Set d = Intersect(Range("I5"), Target)
If d Is Nothing Then Exit Sub
Select Case d
    Case "6H"
        Range("G12:Q30").Font.Color = vbWhite
    Case "4g6g"
        Range("A12:H30").Font.Color = vbWhite
    Case Else
        Range("G12:Q30").Font.Color = vbBlack
        Range("A12:H30").Font.Color = vbBlack
End Select
End Sub

Many Thanks, Tom

hello to all,

I am using a conditional format to highlight name within a list (countif($a$1:$a#25,b1)=1
this is working well but i have a list of names that i add with data validation list and when i add the conditional format to this list the condititional format does not work...can some one help

thanks

How to set data validation for user to key in 24H time format?

Hi,

Is it possible to generate a conditional format based on a validation rule?

What I want to do is:

I added a number of validation rules to my table and I want to find the invalid cells so I can fix them. (I inherited the table, otherwise I would have the rules before data enrty).

I can use the "circle invalid data" function but 1) it takes a long time and 2) when I edit a cell the circles disappear and I need to wait all this time again for them to reappear when clicking the "circle invalid data".

Any ideas?

If I could apply a conditional formatting such that invalid cells will be formatted red it would be awesome, but maybe there are other solutions out there.

Cheers,

Yoav

I have an excel spread sheet were the some of the rows are set on conditional formatting based on some criteria. What is the easiest way to filter out the coloured rows without having to go row by row and hiding the uncolored rows. I have over 1000 of rows with data.

can any one help

thanks,

I have a spreadsheet I am working on for tracking employee performance. I have created drop downs for date, employee name, incident type, description, and manager name. So far so good. I have three things left to do to make it great:

1. I want to add some type of conditional formatting so when the incident is positive it will color the entire row green, and when the incident is negative it will color the row red. Remember I have about 20 different incident types, both positive and negative, in the incident type dropdown.

2. I would like to create filter so I can choose one employee, or a incident type, or date and only see that information. But I don't want to use the standard auto filter as it has too much unneccessary information on it.

3. I currently have the information for the drop downs in a list in hidden cells. So whenever we hire new people we have to unhide that list and add names to the bottom. Is there a way to make an input box for adding data to these drop downs?

Any help you can give on all or some of these topics is greatly appreciated. Even if you know the name of the thing I am trying to do. I assume I may have to get into macros to do this, which is something I have never used. Thanks in advance.

Hi! Everybody

Is it possible with Data Validation that only Enter the Dates in MM/DD/YY Format. in a Column

Thanks in Advance

How can I enter a format for a number that will appear when input as
follows, and also have a Data Validation that ensures there is 8 numbers
typed in

0 1 2 3 4 5 6 7 (not there is a single space between characters)

I have tried format as Custom # # # # # # # # with a Data Validation of 8 -
but this doesn't work where the first number is Zero, I assume Excel doesn't
recognise the Zero and thinks that there is only 7 numbers enter

I need the spacing between each, thanks