Color format cell containing string and numeric values

Question

I have a column in an Excel sheet with both string and numeric values which is city and zip code.

I have tried using vba code to separate the city and zip code which worked fine, but then I could not get the conditional format to work.

I don't want two separate columns, but I want vba code/ color format that color my cells "light green" when the zipcode is greater than 5999 (danish zip code is from 1-10000).

My sheet gets updated once every month or week so the solution should update new cells to be light green .

I have attached an example of the way I want it to look like:

I tried color format like this but also with left(p6;1)="8":

kind regards

additionel question for advanced colorcoding

Answer 1

If the data is in columns A:C, then in Conditional Formatting under Use a formula to determine which cells to format use:

Comma

=AND(NOT(ISERROR(VALUE(LEFT($C1,FIND(" ",$C1)-1)))),IFERROR(VALUE(LEFT($C1,FIND(" ",$C1)-1))>5999,0))

Semi-colon

=AND(NOT(ISERROR(VALUE(LEFT($C1;FIND(" ";$C1)-1))));IFERROR(VALUE(LEFT($C1;FIND(" ";$C1)-1))>5999;0))

Images

Answer 2

yes i did all mandatory things and check it in the sheet that it calculates everything tur or false like in the picture, but when i copypaste the formula into the color formatting it want color the cells if it is true. this is danish sand=True falsk=false see link/image for the screenshot of the sheet with the formula applied enter image description here